import math
from datetime import datetime

import yaml
import pandas as pd
import re
from openpyxl.comments import Comment
from openpyxl.styles import PatternFill
from openpyxl.utils import get_column_letter

rules = {}  # 质控规则
column_map = {}  # 获取列标题映射（列名 -> 列字母）
handle_num = 0  # 单个表格处理数据总数



# 初始化函数------------------------------------------------------------------------------------------
# 加载质控规则
def get_rules():
    global rules
    with open('rules.yaml', 'r', encoding='utf-8') as file:
        rules = yaml.safe_load(file)
        print('质控规则加载完毕......')


# 初始化数据
def initial_data(df):
    """

    :param df: 预处理表格
    :return:
    """
    global column_map
    global handle_num
    # 并获取操作数
    handle_num = len(df)
    print(f'总操作数：{handle_num}')
    # 获取列标题映射（列名 -> 列字母）
    column_map = {col: get_column_letter(idx + 1) for idx, col in enumerate(df.columns)}
    # print(column_map)
    print('数据初始化完毕......')


# 功能函数------------------------------------------------------------------------------------------
# 特殊人群分类
def tsrq(df, ws, col_name, error_list):
    """

    :param df: 预处理表格
    :param ws: 待生成表格
    :param col_name: 特殊人群分类
    :param error_list: 报错列表
    :return:
    """
    color = rules[col_name]['空白']['color']
    # 设置单元格背景色
    fill = PatternFill(
        start_color=color,
        end_color=color,
        fill_type='solid'
    )
    col_letter = column_map[col_name]
    gap_com = rules[col_name]['空白']['comment']
    for row_idx in range(handle_num):

        excel_row = row_idx + 2  # Excel数据行号（标题在第1行）
        cell_value = df.at[row_idx, col_name]
        # print(cell_value)

        try:
            if pd.isna(cell_value):
                cell = ws[f'{col_letter}{excel_row}']
                cell.fill = fill  # 标色
                comment = Comment(gap_com, '')
                cell.comment = comment
            # else:
            #     if cell_value == '医保签约':
            #         cell = ws[f'{col_letter}{excel_row}']
            #         cell.fill = fill  # 标色
        except Exception as e:
            err_info = f"处理单元格 {col_letter}{excel_row} 时出错: {str(e)}"
            print(err_info)
            error_list.append(err_info)


# 性别
def xb(df, ws, col_name, error_list):
    """

    :param df: 预处理表格
    :param ws: 待生成表格
    :param col_name: 性别
    :param error_list: 报错列表
    :return:
    """
    color = rules[col_name]['color']
    # 设置单元格背景色
    fill = PatternFill(
        start_color=color,
        end_color=color,
        fill_type='solid'
    )
    col_letter = column_map[col_name]
    bx_com = rules[col_name]['comment']
    for row_idx in range(handle_num):

        excel_row = row_idx + 2  # Excel数据行号（标题在第1行）
        cell_value = df.at[row_idx, col_name]
        # print(cell_value)
        try:
            if pd.isna(cell_value) or cell_value not in ['男', '女']:
                cell = ws[f'{col_letter}{excel_row}']
                cell.fill = fill  # 标色
                # # 添加批注（如果已有批注则更新）
                comment = Comment(bx_com, '')
                cell.comment = comment
        except Exception as e:
            err_info = f"处理单元格 {col_letter}{excel_row} 时出错: {str(e)}"
            print(err_info)
            error_list.append(err_info)
# 性名
def xm(df, ws, col_name, error_list):
    """

    :param df: 预处理表格
    :param ws: 待生成表格
    :param col_name: 性别
    :param error_list: 报错列表
    :return:
    """

    def is_pure_chinese(name):
        """检查姓名是否仅包含中文（可选包含中文标点）"""
        pattern = r'^[\u4e00-\u9fff·]+$'  # 包含中文字符和间隔号·
        return bool(re.fullmatch(pattern, name))
    color = 'FFFF00'
    # 设置单元格背景色
    fill = PatternFill(
        start_color=color,
        end_color=color,
        fill_type='solid'
    )
    col_letter = column_map[col_name]
    for row_idx in range(handle_num):
        excel_row = row_idx + 2  # Excel数据行号（标题在第1行）
        cell_value = df.at[row_idx, col_name]
        # print(cell_value)
        if not is_pure_chinese(str(cell_value)):
            cell = ws[f'{col_letter}{excel_row}']
            cell.fill = fill  # 标色
            w_com = rules[col_name]['意外字符']['comment']
            comment = Comment(w_com, '')
            cell.comment = comment
        elif ' ' in str(cell_value):
            cell = ws[f'{col_letter}{excel_row}']
            cell.fill = fill  # 标色
            w_com = rules[col_name]['姓名中含空格']['comment']
            comment = Comment(w_com, '')
            cell.comment = comment
        elif str(cell_value)[0] =='.' or str(cell_value)[-1] =='.':
            cell = ws[f'{col_letter}{excel_row}']
            cell.fill = fill  # 标色
            w_com = rules[col_name]['姓名头尾为点']['comment']
            comment = Comment(w_com, '')
            cell.comment = comment

# 身份证号
def sfzh(df, ws, col_name, error_list):
    """

    :param df: 预处理表格
    :param ws: 待生成表格
    :param col_name: 身份证号
    :param error_list: 报错列表
    :return:
    """
    color = rules[col_name]['空白']['color']
    # 设置单元格背景色
    fill = PatternFill(
        start_color=color,
        end_color=color,
        fill_type='solid'
    )
    col_letter = column_map[col_name]
    gap_com = rules[col_name]['空白']['comment']
    dup_com = rules[col_name]['重复']['comment']
    log_com = rules[col_name]['逻辑错误']['comment']
    # 检查身份证号是否重复
    duplicate_mask = df[col_name].duplicated(keep='first')  # 重复时，标记后面的，保留第一个
    # 身份证号逻辑验证
    pattern = r'^[1-9]\d{5}(18|19|20)\d{2}(0[1-9]|1[0-2])(0[1-9]|[12]\d|3[01])\d{3}[\dX]$'

    for row_idx in range(handle_num):
        excel_row = row_idx + 2  # Excel数据行号（标题在第1行）
        cell_value = df.at[row_idx, col_name]
        cell_value = str(cell_value).strip().replace(" ",'')
        # print(cell_value)
        ce_de = duplicate_mask[row_idx]  # True即为重复了

        try:
            if pd.isna(cell_value):
                cell = ws[f'{col_letter}{excel_row}']
                cell.fill = fill  # 标色
                comment = Comment(gap_com, '')
                cell.comment = comment
            else:
                if ce_de:  # 重复了
                    cell = ws[f'{col_letter}{excel_row}']
                    cell.fill = fill  # 标色
                    comment = Comment(dup_com, '')
                    cell.comment = comment
                else:
                    ce_re_result = bool(re.fullmatch(pattern, str(cell_value)))  # False即为不符合匹配
                    if not ce_re_result:  # 逻辑问题
                        cell = ws[f'{col_letter}{excel_row}']
                        cell.fill = fill  # 标色
                        comment = Comment(log_com, '')
                        cell.comment = comment

                def is_valid_id_number(id_str):
                    """检查身份证号是否只包含数字和末尾X"""
                    pattern = r'^\d{17}[\dX]$|^\d{15}$'  # 18位（最后可能是X）或15位纯数字
                    return bool(re.fullmatch(pattern, id_str))
                if len(str(cell_value)) !=18:
                    cell = ws[f'{col_letter}{excel_row}']
                    cell.fill = fill  # 标色
                    w_com = rules[col_name]['长度']['comment']
                    comment = Comment(w_com, '')
                    cell.comment = comment
                elif str(cell_value)[0] ==0:
                    cell = ws[f'{col_letter}{excel_row}']
                    cell.fill = fill  # 标色
                    w_com = rules[col_name]['开头']['comment']
                    comment = Comment(w_com, '')
                    cell.comment = comment
                elif not is_valid_id_number(str(cell_value)):
                    cell = ws[f'{col_letter}{excel_row}']
                    cell.fill = fill  # 标色
                    w_com = rules[col_name]['意外字母']['comment']
                    comment = Comment(w_com, '')
                    cell.comment = comment
        except Exception as e:
            err_info = f"处理单元格 {col_letter}{excel_row} 时出错: {str(e)}"
            print(err_info)
            error_list.append(err_info)


# 工作单位
def gzdw(df, ws, col_name, error_list):
    """

    :param df: 预处理表格
    :param ws: 待生成表格
    :param col_name: 工作单位
    :param error_list: 报错列表
    :return:
    """
    color = rules[col_name]['color']
    # 设置单元格背景色
    fill = PatternFill(
        start_color=color,
        end_color=color,
        fill_type='solid'
    )
    col_letter = column_map[col_name]
    gap_com = rules[col_name]['comment']
    for row_idx in range(handle_num):
        excel_row = row_idx + 2  # Excel数据行号（标题在第1行）
        cell_value = df.at[row_idx, col_name]
        # print(cell_value)
        # 牛街要求工作单位退休或无
        def nj():
            if cell_value =='' or cell_value == "nan" or cell_value == "无":
                cell = ws[f'{col_letter}{excel_row}']
                cell.fill = fill  # 标色
                comment = Comment("工作单位无",'')
                cell.comment = comment
            elif cell_value == "退休":
                cell = ws[f'{col_letter}{excel_row}']
                cell.fill = fill  # 标色
                comment = Comment("工作单位为退休", '')
                cell.comment = comment
        try:
            if pd.isna(cell_value):
                cell = ws[f'{col_letter}{excel_row}']
                cell.fill = fill  # 标色
                comment = Comment(gap_com, '')
                cell.comment = comment
            nj()
        except Exception as e:
            err_info = f"处理单元格 {col_letter}{excel_row} 时出错: {str(e)}"
            print(err_info)
            error_list.append(err_info)


# 联系人姓名
def lxrxm(df, ws, col_name, error_list):
    """

    :param df: 预处理表格
    :param ws: 待生成表格
    :param col_name: 联系人姓名
    :param error_list: 报错列表
    :return:
    """
    color = rules[col_name]['color']
    # 设置单元格背景色
    fill = PatternFill(
        start_color=color,
        end_color=color,
        fill_type='solid'
    )
    col_letter = column_map[col_name]
    gap_com = rules[col_name]['comment']
    for row_idx in range(handle_num):

        excel_row = row_idx + 2  # Excel数据行号（标题在第1行）
        cell_value = df.at[row_idx, col_name]
        # print(cell_value)

        try:
            if pd.isna(cell_value) or cell_value == "无":
                cell = ws[f'{col_letter}{excel_row}']
                cell.fill = fill  # 标色
                comment = Comment(gap_com, '')
                cell.comment = comment

        except Exception as e:
            err_info = f"处理单元格 {col_letter}{excel_row} 时出错: {str(e)}"
            print(err_info)
            error_list.append(err_info)

# 居住状况
def jzqk(df,ws,col_name,error_list):
    color = rules[col_name]['color']
    # 设置单元格背景色
    fill = PatternFill(
        start_color=color,
        end_color=color,
        fill_type="solid"
    )
    color_letter = column_map[col_name]
    jzqk_comment = rules[col_name]['comment']
    for row_idx in range(handle_num):
        excel_row = row_idx+2
        cell_value = df.at[row_idx,col_name]
        try:
            if cell_value == "无":
                cell = ws[f"{color_letter}{excel_row}"]
                cell.fill = fill
                cell.comment = Comment(
                    jzqk_comment,''
                )
        except Exception as e:
            err_info = f"处理单元格 {color_letter}{excel_row} 时出错: {str(e)}"
            print(err_info)
            error_list.append(err_info)
# 籍贯
def jg(df,ws,col_name,error_list):
    color = rules[col_name]['color']
    fill = PatternFill(
        start_color=color,
        end_color=color,
        fill_type="solid"
    )
    col_letter = column_map[col_name]
    jg_comment = rules[col_name]['comment']
    for row_idx in range(handle_num):
        excel_row = row_idx+2
        cell_value = df.at[row_idx,col_name]
        try:
            if cell_value=="无":
                cell = ws[f"{col_letter}{excel_row}"]
                cell.fill = fill
                cell.comment=Comment(
                    jg_comment,''
                )
        except Exception as e:
            err_info = f"处理单元格 {col_letter}{excel_row} 时出错: {str(e)}"
            print(err_info)
            error_list.append(err_info)
# 常驻类型
def czlx(df, ws, col_name, error_list):
    """

    :param df: 预处理表格
    :param ws: 待生成表格
    :param col_name: 常驻类型
    :param error_list: 报错列表
    :return:
    """
    color = rules[col_name]['color']
    # 设置单元格背景色
    fill = PatternFill(
        start_color=color,
        end_color=color,
        fill_type='solid'
    )
    col_letter = column_map[col_name]
    gap_com = rules[col_name]['comment']
    for row_idx in range(handle_num):

        excel_row = row_idx + 2  # Excel数据行号（标题在第1行）
        cell_value = df.at[row_idx, col_name]
        # print(cell_value)

        try:
            if pd.isna(cell_value) or cell_value == '不详':
                cell = ws[f'{col_letter}{excel_row}']
                cell.fill = fill  # 标色
                comment = Comment(gap_com, '')
                cell.comment = comment

        except Exception as e:
            err_info = f"处理单元格 {col_letter}{excel_row} 时出错: {str(e)}"
            print(err_info)
            error_list.append(err_info)


# 民族
def mz(df, ws, col_name, error_list):
    """

    :param df: 预处理表格
    :param ws: 待生成表格
    :param col_name: 民族
    :param error_list: 报错列表
    :return:
    """

    color = rules[col_name]['color']
    # 设置单元格背景色
    fill = PatternFill(
        start_color=color,
        end_color=color,
        fill_type='solid'
    )
    col_letter = column_map[col_name]
    gap_com = rules[col_name]['comment']
    for row_idx in range(handle_num):

        excel_row = row_idx + 2  # Excel数据行号（标题在第1行）
        cell_value = df.at[row_idx, col_name]
        # print(cell_value)

        try:
            if pd.isna(cell_value) or cell_value in ['无', '不详', '其他|请选择']:
                cell = ws[f'{col_letter}{excel_row}']
                cell.fill = fill  # 标色
                comment = Comment(gap_com, '')
                cell.comment = comment

        except Exception as e:
            err_info = f"处理单元格 {col_letter}{excel_row} 时出错: {str(e)}"
            print(err_info)
            error_list.append(err_info)


# 户籍地址
def hjdz(df, ws, col_name, error_list):
    """

    :param df: 预处理表格
    :param ws: 待生成表格
    :param col_name: 户籍地址
    :param error_list: 报错列表
    :return:
    """
    color = rules[col_name]['color']
    # 设置单元格背景色
    fill = PatternFill(
        start_color=color,
        end_color=color,
        fill_type='solid'
    )
    col_letter = column_map[col_name]
    gap_com = rules[col_name]['comment']
    for row_idx in range(handle_num):

        excel_row = row_idx + 2  # Excel数据行号（标题在第1行）
        cell_value = df.at[row_idx, col_name]
        # print(cell_value)

        try:
            if pd.isna(cell_value) or cell_value in ['不详', '无', '未知']:
                cell = ws[f'{col_letter}{excel_row}']
                cell.fill = fill  # 标色
                comment = Comment(gap_com, '')
                cell.comment = comment

        except Exception as e:
            err_info = f"处理单元格 {col_letter}{excel_row} 时出错: {str(e)}"
            print(err_info)
            error_list.append(err_info)


# 血型
def xx(df, ws, col_name, error_list):
    """

    :param df: 预处理表格
    :param ws: 待生成表格
    :param col_name: 血型
    :param error_list: 报错列表
    :return:
    """
    color = rules[col_name]['color']
    # 设置单元格背景色
    fill = PatternFill(
        start_color=color,
        end_color=color,
        fill_type='solid'
    )
    col_letter = column_map[col_name]
    gap_com = rules[col_name]['comment']
    for row_idx in range(handle_num):

        excel_row = row_idx + 2  # Excel数据行号（标题在第1行）
        cell_value = df.at[row_idx, col_name]
        # print(cell_value)

        try:
            if pd.isna(cell_value) or cell_value == '不详':
                cell = ws[f'{col_letter}{excel_row}']
                cell.fill = fill  # 标色
                comment = Comment(gap_com, '')
                cell.comment = comment

        except Exception as e:
            err_info = f"处理单元格 {col_letter}{excel_row} 时出错: {str(e)}"
            print(err_info)
            error_list.append(err_info)


# RH
def rh(df, ws, col_name, error_list):
    """

    :param df: 预处理表格
    :param ws: 待生成表格
    :param col_name: RH
    :param error_list: 报错列表
    :return:
    """
    color = rules[col_name]['color']
    # 设置单元格背景色
    fill = PatternFill(
        start_color=color,
        end_color=color,
        fill_type='solid'
    )
    col_letter = column_map[col_name]
    gap_com = rules[col_name]['comment']
    for row_idx in range(handle_num):

        excel_row = row_idx + 2  # Excel数据行号（标题在第1行）
        cell_value = df.at[row_idx, col_name]
        # print(cell_value)

        try:
            if pd.isna(cell_value):
                cell = ws[f'{col_letter}{excel_row}']
                cell.fill = fill  # 标色
                comment = Comment(gap_com, '')
                cell.comment = comment

        except Exception as e:
            err_info = f"处理单元格 {col_letter}{excel_row} 时出错: {str(e)}"
            print(err_info)
            error_list.append(err_info)


# 医保号
def ybh(df, ws, col_name, error_list):
    """

    :param df: 预处理表格
    :param ws: 待生成表格
    :param col_name: 医保号
    :param error_list: 报错列表
    :return:
    """
    color = rules[col_name]['color']
    # 设置单元格背景色
    fill = PatternFill(
        start_color=color,
        end_color=color,
        fill_type='solid'
    )
    col_letter = column_map[col_name]
    gap_com = rules[col_name]['comment']
    for row_idx in range(handle_num):

        excel_row = row_idx + 2  # Excel数据行号（标题在第1行）
        cell_value = df.at[row_idx, col_name]
        # print(cell_value)
        # 注释医保号不详
        # try:
        #     if pd.isna(cell_value) or str(cell_value) in ['不详', '无']:
        #         cell = ws[f'{col_letter}{excel_row}']
        #         cell.fill = fill  # 标色
        #         comment = Comment(gap_com, '')
        #         cell.comment = comment
        #
        # except Exception as e:
        #     err_info = f"处理单元格 {col_letter}{excel_row} 时出错: {str(e)}"
        #     print(err_info)
        #     error_list.append(err_info)


# 现住址详细地址
def xxdz(df, ws, col_name, error_list):
    """

    :param df: 预处理表格
    :param ws: 待生成表格
    :param col_name: 现住址详细地址
    :param error_list: 报错列表
    :return:
    """
    color = rules[col_name]['空白']['color']
    # 设置单元格背景色
    fill = PatternFill(
        start_color=color,
        end_color=color,
        fill_type='solid'
    )
    col_letter = column_map[col_name]
    gap_com = rules[col_name]['空白']['comment']
    bx_com = rules[col_name]['不详']['comment']
    pattern = r'\d'
    for row_idx in range(handle_num):

        excel_row = row_idx + 2  # Excel数据行号（标题在第1行）
        cell_value = df.at[row_idx, col_name]
        # print(cell_value)

        try:
            if pd.isna(cell_value):
                cell = ws[f'{col_letter}{excel_row}']
                cell.fill = fill  # 标色
                comment = Comment(gap_com, '')
                cell.comment = comment
            else:
                if not bool(re.search(pattern, cell_value)):
                    cell = ws[f'{col_letter}{excel_row}']
                    cell.fill = fill  # 标色
                    comment = Comment(bx_com, '')
                    cell.comment = comment

        except Exception as e:
            err_info = f"处理单元格 {col_letter}{excel_row} 时出错: {str(e)}"
            print(err_info)
            error_list.append(err_info)


# 职业
def zy(df, ws, col_name1, col_name2, error_list):
    """

    :param df: 预处理表格
    :param ws: 待生成表格
    :param col_name1: 工作单位
    :param col_name2: 职业
    :param error_list: 报错列表
    :return:
    """
    color = rules[col_name2]['空白']['color']
    # 设置单元格背景色
    fill = PatternFill(
        start_color=color,
        end_color=color,
        fill_type='solid'
    )
    col_letter = column_map[col_name2]
    gap_com = rules[col_name2]['空白']['comment']
    wu_com = rules[col_name2]['无工作单位']['comment']
    for row_idx in range(handle_num):

        excel_row = row_idx + 2  # Excel数据行号（标题在第1行）
        cell_value = df.at[row_idx, col_name2]  # 职业
        # print(cell_value)

        try:
            if pd.isna(cell_value):
                cell = ws[f'{col_letter}{excel_row}']
                cell.fill = fill  # 标色
                comment = Comment(gap_com, '')
                cell.comment = comment
            else:
                cell_value1 = df.at[row_idx, col_name1]  # 工作单位
                if not pd.isna(cell_value1):
                    if cell_value1 in ['无', '退休'] and cell_value not in ['其他', '无职业', '无']:
                        cell = ws[f'{col_letter}{excel_row}']
                        cell.fill = fill  # 标色
                        comment = Comment(wu_com, '')
                        cell.comment = comment

        except Exception as e:
            err_info = f"处理单元格 {col_letter}{excel_row} 时出错: {str(e)}"
            print(err_info)
            error_list.append(err_info)
        if cell_value == "婴幼儿":
            cell = ws[f"{col_letter}{excel_row}"]
            cell.fill =PatternFill(
                start_color=None,
                end_color=None,
                fill_type=None
            )
            if cell.comment:  # 先检查是否存在批注
                cell.comment = None  # 等同于删除
        cell_value1 = df.at[row_idx, col_name1]  # 工作单位
        if cell_value =="其他" and (pd.isna(cell_value1) or cell_value1=="无"):
            cell = ws[f"{col_letter}{excel_row}"]
            cell.fill = fill
            cell.comment = Comment(
                "职业为其他，工作单位是无",''
            )
def hkszd(df,ws,col_name,error_list,col_name_1,col_name_2):
    color = rules[col_name]['未选择']['color']
    hkszd = rules[col_name]['未选择']['comment']
    col_letter = column_map[col_name]
    fill = PatternFill(
        start_color=color,
        end_color=color,
        fill_type='solid'
    )
    for row_idx in range(handle_num):
        excel_row = row_idx+2
        cell_value = df.at[row_idx,col_name]
        cell_value_1 = df.at[row_idx,col_name_1]
        cell_value_2 = df.at[row_idx,col_name_2]
        cell = ws[f"{col_letter}{excel_row}"]
        try:
            if pd.isna(cell_value_2):
                cell.fill = fill
                cell.comment = Comment(
                    "户籍地址为空", ''
                )
            else:
                if "北京" in cell_value and "户籍" not in cell_value_1:
                    cell.fill = fill
                    cell.comment = Comment(
                        "户口所在地为北京，但常驻类型非户籍", ''
                    )
                if "北京" in cell_value and "北京" not in cell_value_2:
                    cell.fill = fill
                    cell.comment = Comment(
                        "户口所在地为北京，但户籍地址非北京", ''
                    )
                if "北京" in cell_value and "户籍" not in cell_value_1 and "北京" not in cell_value_2:
                    cell.fill = fill
                    cell.comment = Comment(
                        "户口所在地为北京，但常驻类型非户籍,户籍地址非北京", ''
                    )
                if "北京" not in cell_value and "户籍" == cell_value_1:
                    cell.fill = fill
                    cell.comment = Comment(
                        "户口所在地非北京，但常驻类型为户籍", ''
                    )
                if "北京" not in cell_value and "北京" in cell_value_2:
                    cell.fill = fill
                    cell.comment = Comment(
                        "户口所在地非北京，但户籍地址为北京", ''
                    )
                if "北京" not in cell_value and "户籍" == cell_value_1 and "北京" in cell_value_2:
                    cell.fill = fill
                    cell.comment = Comment(
                        "户口所在地非北京，但常驻类型为户籍,户籍地址为北京", ''
                    )
                if pd.isna(cell_value):
                    cell.fill = fill
                    cell.comment=Comment(
                        "户口所在地为无",''
                    )
                elif "请选择" in cell_value:
                        cell.fill = fill
                        cell.comment = Comment(
                            "户口所在地未选择",''
                        )
        except Exception as e:
            err_info = f"处理{col_letter}{excel_row}时出现异常:{str(e)}"
            print(err_info)
            error_list.append(err_info)
# BMI数据异常
def bmi(df,ws,col_name,error_list):
    color = rules[col_name]['高']['color']
    comment_g = rules[col_name]['高']['comment']
    comment_d = rules[col_name]['低']['comment']
    fill = PatternFill(
        start_color=color,
        end_color=color,
        fill_type='solid'
    )
    col_letter = column_map[col_name]
    for row_idx in range(handle_num):
        excel_row = row_idx+2
        cell_value = df.at[row_idx,col_name]
        cell = ws[f"{col_letter}{excel_row}"]
        try:
            if float(cell_value) >=40:

                cell.fill = fill
                cell.comment = Comment(
                    comment_g,''
                )
            elif float(cell_value)<=15:
                cell.fill = fill
                cell.comment = Comment(
                    comment_d, ''
                )
        except Exception as e:
            error_info = f"处理{col_letter}{excel_row}单元格遇见异常,{str(e)}"
            print(error_info)
            error_list.append(error_info)

# 医疗费用支付方式
def ylfy(df, ws, col_name1, col_name2, error_list):
    """

    :param df: 预处理表格
    :param ws: 待生成表格
    :param col_name1: 工作单位
    :param col_name2: 医疗费用支付方式
    :param error_list: 报错列表
    :return:
    """
    color = rules[col_name2]['空白']['color']
    # 设置单元格背景色
    fill = PatternFill(
        start_color=color,
        end_color=color,
        fill_type='solid'
    )
    col_letter = column_map[col_name2]
    gap_com = rules[col_name2]['空白']['comment']
    wu_com = rules[col_name2]['无工作单位']['comment']
    for row_idx in range(handle_num):

        excel_row = row_idx + 2  # Excel数据行号（标题在第1行）
        cell_value = df.at[row_idx, col_name2]  # 职业
        # print(cell_value)

        try:
            if pd.isna(cell_value):
                cell = ws[f'{col_letter}{excel_row}']
                cell.fill = fill  # 标色
                comment = Comment(gap_com, '')
                cell.comment = comment
            else:
                cell_value1 = df.at[row_idx, col_name1]  # 工作单位
                if not pd.isna(cell_value1):
                    if cell_value1 in ['无', '退休'] and '职工' in cell_value:
                        cell = ws[f'{col_letter}{excel_row}']
                        cell.fill = fill  # 标色
                        comment = Comment(wu_com, '')
                        cell.comment = comment

        except Exception as e:
            err_info = f"处理单元格 {col_letter}{excel_row} 时出错: {str(e)}"
            print(err_info)
            error_list.append(err_info)


# 药物过敏史
def yms(df, ws, col_name, error_list):
    """

    :param df: 预处理表格
    :param ws: 待生成表格
    :param col_name: 药物过敏史
    :param error_list: 报错列表
    :return:
    """
    color = rules[col_name]['color']
    # 设置单元格背景色
    fill = PatternFill(
        start_color=color,
        end_color=color,
        fill_type='solid'
    )
    col_letter = column_map[col_name]
    gap_com = rules[col_name]['comment']
    for row_idx in range(handle_num):

        excel_row = row_idx + 2  # Excel数据行号（标题在第1行）
        cell_value = df.at[row_idx, col_name]
        # print(cell_value)

        try:
            if pd.isna(cell_value):
                cell = ws[f'{col_letter}{excel_row}']
                cell.fill = fill  # 标色
                comment = Comment(gap_com, '')
                cell.comment = comment

        except Exception as e:
            err_info = f"处理单元格 {col_letter}{excel_row} 时出错: {str(e)}"
            print(err_info)
            error_list.append(err_info)


# 暴露史
def bls(df, ws, col_name, error_list):
    """

    :param df: 预处理表格
    :param ws: 待生成表格
    :param col_name: 暴露史
    :param error_list: 报错列表
    :return:
    """
    color = rules[col_name]['color']
    # 设置单元格背景色
    fill = PatternFill(
        start_color=color,
        end_color=color,
        fill_type='solid'
    )
    col_letter = column_map[col_name]
    gap_com = rules[col_name]['comment']
    for row_idx in range(handle_num):

        excel_row = row_idx + 2  # Excel数据行号（标题在第1行）
        cell_value = df.at[row_idx, col_name]
        # print(cell_value)

        try:
            if pd.isna(cell_value):
                cell = ws[f'{col_letter}{excel_row}']
                cell.fill = fill  # 标色
                comment = Comment(gap_com, '')
                cell.comment = comment

        except Exception as e:
            err_info = f"处理单元格 {col_letter}{excel_row} 时出错: {str(e)}"
            print(err_info)
            error_list.append(err_info)


# 疾病史
def jbs(df, ws, col_name, error_list):
    """

    :param df: 预处理表格
    :param ws: 待生成表格
    :param col_name: 疾病史
    :param error_list: 报错列表
    :return:
    """
    color = rules[col_name]['空白']['color']
    # 设置单元格背景色
    fill = PatternFill(
        start_color=color,
        end_color=color,
        fill_type='solid'
    )
    # jbs = eval(df.at[0, col_name]) # 字符转代码
    # has_empty_feild = any(item.get('确诊日期')  in (None, "") for item in jbs) # 确诊日期字段是否存在，须先判断单元格是否为空
    # print(has_empty_feild)
    # d = ' '
    # print(d.isspace())
    col_letter = column_map[col_name]
    gap_com = rules[col_name]['空白']['comment']
    time_com = rules[col_name]['时间空项']['comment']
    for row_idx in range(handle_num):

        excel_row = row_idx + 2  # Excel数据行号（标题在第1行）
        cell_value = df.at[row_idx, col_name]

        try:
            if pd.isna(cell_value):
                cell = ws[f'{col_letter}{excel_row}']
                cell.fill = fill  # 标色
                comment = Comment(gap_com, '')
                cell.comment = comment
            else:
                if cell_value != "无":
                    has_empty_str = any(
                        qzrq.get('确诊日期') == "" or qzrq.get('确诊日期').isspace() for qzrq in
                        eval(cell_value))  # 确诊日期是否为空
                    # print(has_empty_str)
                    if has_empty_str:
                        cell = ws[f'{col_letter}{excel_row}']
                        cell.fill = fill  # 标色
                        comment = Comment(time_com, '')
                        cell.comment = comment

        except Exception as e:
            # err_info = f"处理单元格 {col_letter}{excel_row} 时出错: {str(e)}"
            # print(err_info)
            # error_list.append(err_info)
            pass


# 手术史
def sss(df, ws, col_name, error_list):
    """

    :param df: 预处理表格
    :param ws: 待生成表格
    :param col_name: 手术史
    :param error_list: 报错列表
    :return:
    """
    color = rules[col_name]['color']
    # 设置单元格背景色
    fill = PatternFill(
        start_color=color,
        end_color=color,
        fill_type='solid'
    )
    col_letter = column_map[col_name]
    gap_com = rules[col_name]['comment']
    for row_idx in range(handle_num):

        excel_row = row_idx + 2  # Excel数据行号（标题在第1行）
        cell_value = df.at[row_idx, col_name]
        # print(cell_value)

        try:
            if pd.isna(cell_value):
                cell = ws[f'{col_letter}{excel_row}']
                cell.fill = fill  # 标色
                comment = Comment(gap_com, '')
                cell.comment = comment

        except Exception as e:
            err_info = f"处理单元格 {col_letter}{excel_row} 时出错: {str(e)}"
            print(err_info)
            error_list.append(err_info)


# 外伤史
def wss(df, ws, col_name, error_list):
    """

    :param df: 预处理表格
    :param ws: 待生成表格
    :param col_name: 外伤史
    :param error_list: 报错列表
    :return:
    """
    color = rules[col_name]['color']
    # 设置单元格背景色
    fill = PatternFill(
        start_color=color,
        end_color=color,
        fill_type='solid'
    )
    col_letter = column_map[col_name]
    gap_com = rules[col_name]['comment']
    for row_idx in range(handle_num):

        excel_row = row_idx + 2  # Excel数据行号（标题在第1行）
        cell_value = df.at[row_idx, col_name]
        # print(cell_value)

        try:
            if pd.isna(cell_value):
                cell = ws[f'{col_letter}{excel_row}']
                cell.fill = fill  # 标色
                comment = Comment(gap_com, '')
                cell.comment = comment

        except Exception as e:
            err_info = f"处理单元格 {col_letter}{excel_row} 时出错: {str(e)}"
            print(err_info)
            error_list.append(err_info)


# 输血史
def sxs(df, ws, col_name, error_list):
    """

    :param df: 预处理表格
    :param ws: 待生成表格
    :param col_name: 输血史
    :param error_list: 报错列表
    :return:
    """
    color = rules[col_name]['color']
    # 设置单元格背景色
    fill = PatternFill(
        start_color=color,
        end_color=color,
        fill_type='solid'
    )
    col_letter = column_map[col_name]
    gap_com = rules[col_name]['comment']
    for row_idx in range(handle_num):

        excel_row = row_idx + 2  # Excel数据行号（标题在第1行）
        cell_value = df.at[row_idx, col_name]
        # print(cell_value)

        try:
            if pd.isna(cell_value):
                cell = ws[f'{col_letter}{excel_row}']
                cell.fill = fill  # 标色
                comment = Comment(gap_com, '')
                cell.comment = comment

        except Exception as e:
            err_info = f"处理单元格 {col_letter}{excel_row} 时出错: {str(e)}"
            print(err_info)
            error_list.append(err_info)


# 残疾情况
def cjqk(df, ws, col_name, error_list):
    """

    :param df: 预处理表格
    :param ws: 待生成表格
    :param col_name: 残疾情况
    :param error_list: 报错列表
    :return:
    """
    color = rules[col_name]['color']
    # 设置单元格背景色
    fill = PatternFill(
        start_color=color,
        end_color=color,
        fill_type='solid'
    )
    col_letter = column_map[col_name]
    gap_com = rules[col_name]['comment']
    for row_idx in range(handle_num):

        excel_row = row_idx + 2  # Excel数据行号（标题在第1行）
        cell_value = df.at[row_idx, col_name]
        # print(cell_value)

        try:
            if pd.isna(cell_value):
                cell = ws[f'{col_letter}{excel_row}']
                cell.fill = fill  # 标色
                comment = Comment(gap_com, '')
                cell.comment = comment

        except Exception as e:
            err_info = f"处理单元格 {col_letter}{excel_row} 时出错: {str(e)}"
            print(err_info)
            error_list.append(err_info)


# 厨房排风设施
def pfss(df, ws, col_name, error_list):
    """

    :param df: 预处理表格
    :param ws: 待生成表格
    :param col_name: 厨房排风设施
    :param error_list: 报错列表
    :return:
    """
    color = rules[col_name]['color']
    # 设置单元格背景色
    fill = PatternFill(
        start_color=color,
        end_color=color,
        fill_type='solid'
    )
    col_letter = column_map[col_name]
    gap_com = rules[col_name]['comment']
    for row_idx in range(handle_num):

        excel_row = row_idx + 2  # Excel数据行号（标题在第1行）
        cell_value = df.at[row_idx, col_name]
        # print(cell_value)

        try:
            if pd.isna(cell_value):
                cell = ws[f'{col_letter}{excel_row}']
                cell.fill = fill  # 标色
                comment = Comment(gap_com, '')
                cell.comment = comment

        except Exception as e:
            err_info = f"处理单元格 {col_letter}{excel_row} 时出错: {str(e)}"
            print(err_info)
            error_list.append(err_info)


# 燃料类型
def rllx(df, ws, col_name, error_list):
    """

    :param df: 预处理表格
    :param ws: 待生成表格
    :param col_name: 燃料类型
    :param error_list: 报错列表
    :return:
    """
    color = rules[col_name]['color']
    # 设置单元格背景色
    fill = PatternFill(
        start_color=color,
        end_color=color,
        fill_type='solid'
    )
    col_letter = column_map[col_name]
    gap_com = rules[col_name]['comment']
    for row_idx in range(handle_num):

        excel_row = row_idx + 2  # Excel数据行号（标题在第1行）
        cell_value = df.at[row_idx, col_name]
        # print(cell_value)

        try:
            if pd.isna(cell_value):
                cell = ws[f'{col_letter}{excel_row}']
                cell.fill = fill  # 标色
                comment = Comment(gap_com, '')
                cell.comment = comment

        except Exception as e:
            err_info = f"处理单元格 {col_letter}{excel_row} 时出错: {str(e)}"
            print(err_info)
            error_list.append(err_info)


# 饮水
def ys(df, ws, col_name, error_list):
    """

    :param df: 预处理表格
    :param ws: 待生成表格
    :param col_name: 饮水
    :param error_list: 报错列表
    :return:
    """
    color = rules[col_name]['color']
    # 设置单元格背景色
    fill = PatternFill(
        start_color=color,
        end_color=color,
        fill_type='solid'
    )
    col_letter = column_map[col_name]
    gap_com = rules[col_name]['comment']
    for row_idx in range(handle_num):

        excel_row = row_idx + 2  # Excel数据行号（标题在第1行）
        cell_value = df.at[row_idx, col_name]
        # print(cell_value)

        try:
            if pd.isna(cell_value):
                cell = ws[f'{col_letter}{excel_row}']
                cell.fill = fill  # 标色
                comment = Comment(gap_com, '')
                cell.comment = comment

        except Exception as e:
            err_info = f"处理单元格 {col_letter}{excel_row} 时出错: {str(e)}"
            print(err_info)
            error_list.append(err_info)


# 厕所
def cs(df, ws, col_name, error_list):
    """

    :param df: 预处理表格
    :param ws: 待生成表格
    :param col_name: 厕所
    :param error_list: 报错列表
    :return:
    """
    color = rules[col_name]['color']
    # 设置单元格背景色
    fill = PatternFill(
        start_color=color,
        end_color=color,
        fill_type='solid'
    )
    col_letter = column_map[col_name]
    gap_com = rules[col_name]['comment']
    for row_idx in range(handle_num):

        excel_row = row_idx + 2  # Excel数据行号（标题在第1行）
        cell_value = df.at[row_idx, col_name]
        # print(cell_value)

        try:
            if pd.isna(cell_value):
                cell = ws[f'{col_letter}{excel_row}']
                cell.fill = fill  # 标色
                comment = Comment(gap_com, '')
                cell.comment = comment

        except Exception as e:
            err_info = f"处理单元格 {col_letter}{excel_row} 时出错: {str(e)}"
            print(err_info)
            error_list.append(err_info)


# 禽畜栏
def qcl(df, ws, col_name, error_list):
    """

    :param df: 预处理表格
    :param ws: 待生成表格
    :param col_name: 禽畜栏
    :param error_list: 报错列表
    :return:
    """
    color = rules[col_name]['color']
    # 设置单元格背景色
    fill = PatternFill(
        start_color=color,
        end_color=color,
        fill_type='solid'
    )
    col_letter = column_map[col_name]
    gap_com = rules[col_name]['comment']
    for row_idx in range(handle_num):

        excel_row = row_idx + 2  # Excel数据行号（标题在第1行）
        cell_value = df.at[row_idx, col_name]
        # print(cell_value)

        try:
            if pd.isna(cell_value):
                cell = ws[f'{col_letter}{excel_row}']
                cell.fill = fill  # 标色
                comment = Comment(gap_com, '')
                cell.comment = comment

        except Exception as e:
            err_info = f"处理单元格 {col_letter}{excel_row} 时出错: {str(e)}"
            print(err_info)
            error_list.append(err_info)


# 责任医生
def zrys(df, ws, col_name, error_list):
    """

    :param df: 预处理表格
    :param ws: 待生成表格
    :param col_name: 责任医生
    :param error_list: 报错列表
    :return:
    """
    color = rules[col_name]['color']
    # 设置单元格背景色
    fill = PatternFill(
        start_color=color,
        end_color=color,
        fill_type='solid'
    )
    col_letter = column_map[col_name]
    gap_com = rules[col_name]['comment']
    for row_idx in range(handle_num):

        excel_row = row_idx + 2  # Excel数据行号（标题在第1行）
        cell_value = df.at[row_idx, col_name]
        # print(cell_value)

        try:
            if pd.isna(cell_value):
                cell = ws[f'{col_letter}{excel_row}']
                cell.fill = fill  # 标色
                comment = Comment(gap_com, '')
                cell.comment = comment

        except Exception as e:
            err_info = f"处理单元格 {col_letter}{excel_row} 时出错: {str(e)}"
            print(err_info)
            error_list.append(err_info)


# 婚姻状况
def hy(df, ws, col_name1, col_name2, error_list):
    """

    :param df: 预处理表格
    :param ws: 待生成表格
    :param col_name1: 身份证号
    :param col_name2: 婚姻状况
    :param error_list: 报错列表
    :return:
    """

    color = rules[col_name2]['空白']['color']
    # 设置单元格背景色
    fill = PatternFill(
        start_color=color,
        end_color=color,
        fill_type='solid'
    )
    child_com = rules[col_name2]['儿童']['comment']
    col_letter = column_map[col_name2]

    pattern = r'^[1-9]\d{5}(18|19|20)\d{2}(0[1-9]|1[0-2])(0[1-9]|[12]\d|3[01])\d{3}[\dX]$'
    current_year = datetime.now().year  # 获取当前年份
    for row_idx in range(handle_num):

        excel_row = row_idx + 2  # Excel数据行号（标题在第1行）
        cell_value = df.at[row_idx, col_name2]

        # print(age,cell_value)
        try:
            if pd.isna(cell_value):
                cell = ws[f'{col_letter}{excel_row}']
                cell.fill = fill  # 标色
            else:
                # 儿童婚姻->需要身份证进行年龄计算
                sfzh = df.at[row_idx, col_name1]
                if not pd.isna(sfzh):
                    sfzh = str(sfzh)
                    # 当身份证号本身出现问题，则不再进行下一步操作，默认成人
                    if bool(re.match(pattern, sfzh)):
                        birth_year = int(sfzh[6:10])
                        # current_year = datetime.now().year
                        age = current_year - birth_year
                        if age < 22 and cell_value == '已婚':
                            cell = ws[f'{col_letter}{excel_row}']
                            cell.fill = fill  # 标色
                            # 添加批注（如果已有批注则更新）
                            comment = Comment(child_com, '')
                            cell.comment = comment

        except Exception as e:
            err_info = f"处理单元格 {col_letter}{excel_row} 时出错: {str(e)}"
            print(err_info)
            error_list.append(err_info)


# 文化程度
# def whcd(df, ws, col_name1, col_name2, error_list):
#     """
#
#     :param df: 预处理表格
#     :param ws: 待生成表格
#     :param col_name1: 身份证号
#     :param col_name2: 文化程度
#     :param error_list: 错误列表
#     :return:
#     """
#     color = rules[col_name2]['不详']['color']
#     # 设置单元格背景色
#     fill = PatternFill(
#         start_color=color,
#         end_color=color,
#         fill_type='solid'
#     )
#
#     _1to5_com = rules[col_name2]['儿童']['1to5']['comment']
#     _6to12_com = rules[col_name2]['儿童']['6to12']['comment']
#     _13to15_com = rules[col_name2]['儿童']['13to15']['comment']
#     _16to18_com = rules[col_name2]['儿童']['16to18']['comment']
#     col_letter = column_map[col_name2]
#
#     pattern = r'^[1-9]\d{5}(18|19|20)\d{2}(0[1-9]|1[0-2])(0[1-9]|[12]\d|3[01])\d{3}[\dX]$'
#     current_year = datetime.now().year
#     for row_idx in range(handle_num):
#
#         excel_row = row_idx + 2  # Excel数据行号（标题在第1行）
#         cell_value = df.at[row_idx, col_name2]
#
#         try:
#             if pd.isna(cell_value) or cell_value == '不详':
#                 cell = ws[f'{col_letter}{excel_row}']
#                 cell.fill = fill  # 标色
#             else:
#                 sfzh = df.at[row_idx, col_name1]
#                 age = 22
#                 # 当身份证号本身出现问题，则不再进行下一步操作，默认成人
#                 if not pd.isna(sfzh):
#                     if bool(re.match(pattern, sfzh)):
#                         birth_year = int(sfzh[6:10])
#                         age = current_year - birth_year
#                         if 1 <= age <= 5:
#                             if cell_value != '学龄前':
#                                 cell = ws[f'{col_letter}{excel_row}']
#                                 cell.fill = fill  # 标色
#
#                                 comment = Comment(_1to5_com, '')
#                                 cell.comment = comment
#                         elif 6 <= age <= 12:
#                             if cell_value != '小学':
#                                 cell = ws[f'{col_letter}{excel_row}']
#                                 cell.fill = fill  # 标色
#
#                                 comment = Comment(_6to12_com, '')
#                                 cell.comment = comment
#                         elif 13 <= age <= 15:
#                             if cell_value != '初中':
#                                 cell = ws[f'{col_letter}{excel_row}']
#                                 cell.fill = fill  # 标色
#
#                                 comment = Comment(_13to15_com, '')
#                                 cell.comment = comment
#                         elif 16 <= age <= 18:
#                             if cell_value != '高中/技校/中专':
#                                 cell = ws[f'{col_letter}{excel_row}']
#                                 cell.fill = fill  # 标色
#
#                                 comment = Comment(_16to18_com, '')
#                                 cell.comment = comment
#                         else:
#                             pass
#
#         except Exception as e:
#             err_info = f"处理单元格 {col_letter}{excel_row} 时出错: {str(e)}"
#             print(err_info)
#             error_list.append(err_info)


# 本人电话
def brdh(df, ws, col_name, error_list):
    """

    :param df: 预处理表格
    :param ws: 待生成表格
    :param col_name: 本人电话
    :param error_list: 错误列表
    :return:
    """
    color = rules[col_name]['不详']['color']
    # 设置单元格背景色
    fill = PatternFill(
        start_color=color,
        end_color=color,
        fill_type='solid'
    )
    gap_com = rules[col_name]['不详']['comment']
    ws_com = rules[col_name]['位数']['comment']
    col_letter = column_map[col_name]
    # print(df.at[0, col_name])
    for row_idx in range(handle_num):

        excel_row = row_idx + 2  # Excel数据行号（标题在第1行）
        cell_value = df.at[row_idx, col_name]
        # print(cell_value)

        try:
            if pd.isna(cell_value):
                cell = ws[f'{col_letter}{excel_row}']
                cell.fill = fill  # 标色

                comment = Comment(gap_com, '')
                cell.comment = comment
            else:
                cell_value = str(cell_value)
                if cell_value in ['无', '1', '0', '拒绝提供']:
                    cell = ws[f'{col_letter}{excel_row}']
                    cell.fill = fill  # 标色

                    comment = Comment(gap_com, '')
                    cell.comment = comment
                elif len(cell_value) not in [11, 8, 12]:  # 对应普通电话、8位座机、带前缀号码（如：0311-88888888）
                    cell = ws[f'{col_letter}{excel_row}']
                    cell.fill = fill  # 标色

                    comment = Comment(ws_com, '')
                    cell.comment = comment

        except Exception as e:
            err_info = f"处理单元格 {col_letter}{excel_row} 时出错: {str(e)}"
            print(err_info)
            error_list.append(err_info)
# 文化程度
def daxz(df,ws,col_name,error_list):
    fill = PatternFill(
        start_color="FFFF00",
        end_color="FFFF00",
        fill_type='solid'
    )
    col_letter = column_map[col_name]
    # print(df.at[0, col_name])
    for row_idx in range(handle_num):
        excel_row = row_idx + 2  # Excel数据行号（标题在第1行）
        cell_value = df.at[row_idx, col_name]
        if pd.isna(cell_value):
            cell = ws[f'{col_letter}{excel_row}']
            cell.fill = fill  # 标色

            comment = Comment("文化程度未填写", '')
            cell.comment = comment
        if cell_value =="不详":
            cell = ws[f'{col_letter}{excel_row}']
            cell.fill = fill  # 标色
            comment = Comment("文化程度不详", '')
            cell.comment = comment
# 联系人电话
def lxrdh(df, ws, col_name, error_list):
    """

    :param df: 预处理表格
    :param ws: 待生成表格
    :param col_name: 联系人电话
    :param error_list: 错误列表
    :return:
    """
    color = rules[col_name]['不详']['color']
    # 设置单元格背景色
    fill = PatternFill(
        start_color=color,
        end_color=color,
        fill_type='solid'
    )
    gap_com = rules[col_name]['不详']['comment']
    ws_com = rules[col_name]['位数']['comment']
    col_letter = column_map[col_name]
    # print(df.at[0, col_name])
    for row_idx in range(handle_num):

        excel_row = row_idx + 2  # Excel数据行号（标题在第1行）
        cell_value = df.at[row_idx, col_name]
        # print(cell_value)

        try:
            if pd.isna(cell_value):
                cell = ws[f'{col_letter}{excel_row}']
                cell.fill = fill  # 标色

                comment = Comment(gap_com, '')
                cell.comment = comment
            else:
                cell_value = str(cell_value)
                if cell_value in ['无', '1', '0', '拒绝提供']:
                    cell = ws[f'{col_letter}{excel_row}']
                    cell.fill = fill  # 标色

                    comment = Comment(gap_com, '')
                    cell.comment = comment
                elif len(cell_value) not in [11, 8, 12]:  # 对应普通电话、8位座机、带前缀号码（如：0311-88888888）
                    cell = ws[f'{col_letter}{excel_row}']
                    cell.fill = fill  # 标色

                    comment = Comment(ws_com, '')
                    cell.comment = comment

        except Exception as e:
            err_info = f"处理单元格 {col_letter}{excel_row} 时出错: {str(e)}"
            print(err_info)
            error_list.append(err_info)


# 同住者
def tzz(df, ws, col_name1, col_name2, col_name3, error_list):
    """

    :param df: 预处理表格
    :param ws: 待生成表格
    :param col_name1: 身份证号
    :param col_name2: 婚姻状况
    :param col_name3: 同住者
    :param error_list: 错误列表
    :return:
    """
    color = rules[col_name3]['空白']['color']
    # 设置单元格背景色
    fill = PatternFill(
        start_color=color,
        end_color=color,
        fill_type='solid'
    )
    ww_man_com = rules[col_name3]['未婚']['成人']['comment']
    ww_child_com = rules[col_name3]['未婚']['儿童']['comment']
    col_letter = column_map[col_name3]

    pattern = r'^[1-9]\d{5}(18|19|20)\d{2}(0[1-9]|1[0-2])(0[1-9]|[12]\d|3[01])\d{3}[\dX]$'
    current_year = datetime.now().year
    # print(df.at[0, col_name3].split(','))

    for row_idx in range(handle_num):

        excel_row = row_idx + 2  # Excel数据行号（标题在第1行）
        cell_value = df.at[row_idx, col_name3]
        # print(cell_value)

        try:
            if pd.isna(cell_value):
                cell = ws[f'{col_letter}{excel_row}']
                cell.fill = fill  # 标色
            else:
                sfzh = df.at[row_idx, col_name1]
                # 当身份证号本身出现问题，则不再进行下一步操作，默认成人
                if not pd.isna(sfzh):
                    sfzh = str(sfzh)
                    if bool(re.match(pattern, sfzh)):
                        birth_year = int(sfzh[6:10])
                        age = current_year - birth_year
                        if age >= 22:
                            hy = df.at[row_idx, col_name2]
                            if not pd.isna(hy) and hy == '未婚':
                                if '配偶' in cell_value.split(','):
                                    cell = ws[f'{col_letter}{excel_row}']
                                    cell.fill = fill  # 标色

                                    comment = Comment(ww_man_com, '')
                                    cell.comment = comment

                        else:  # 年龄低于22，默认儿童，不应有配偶和子女
                            if '配偶' in cell_value.split(',') or '子女' in cell_value.split(','):
                                cell = ws[f'{col_letter}{excel_row}']
                                cell.fill = fill  # 标色

                                comment = Comment(ww_child_com, '')
                                cell.comment = comment

        except Exception as e:
            err_info = f"处理单元格 {col_letter}{excel_row} 时出错: {str(e)}"
            print(err_info)
            error_list.append(err_info)


# 建档人
def jdr(df, ws, col_name, error_list):
    """

    :param df: 预处理表格
    :param ws: 待生成表格
    :param col_name: 建档人
    :param error_list: 错误列表
    :return:
    """
    color = rules[col_name]['color']
    # 设置单元格背景色
    fill = PatternFill(
        start_color=color,
        end_color=color,
        fill_type='solid'
    )
    gap_com = rules[col_name]['comment']
    col_letter = column_map[col_name]

    for row_idx in range(handle_num):

        excel_row = row_idx + 2  # Excel数据行号（标题在第1行）
        cell_value = df.at[row_idx, col_name]
        # print(cell_value)

        try:
            if pd.isna(cell_value) or cell_value in ['无', '不详']:
                cell = ws[f'{col_letter}{excel_row}']
                cell.fill = fill  # 标色

                comment = Comment(gap_com, '')
                cell.comment = comment

        except Exception as e:
            err_info = f"处理单元格 {col_letter}{excel_row} 时出错: {str(e)}"
            print(err_info)
            error_list.append(err_info)


# 户别
def hb(df, ws, col_name, error_list):
    """

    :param df: 预处理表格
    :param ws: 待生成表格
    :param col_name: 户别
    :param error_list: 错误列表
    :return:
    """
    color = rules[col_name]['color']
    # 设置单元格背景色
    fill = PatternFill(
        start_color=color,
        end_color=color,
        fill_type='solid'
    )
    gap_com = rules[col_name]['comment']
    col_letter = column_map[col_name]
    for row_idx in range(handle_num):

        excel_row = row_idx + 2  # Excel数据行号（标题在第1行）
        cell_value = df.at[row_idx, col_name]
        # print(cell_value)

        try:
            if pd.isna(cell_value) or cell_value in ['无', '不详']:
                cell = ws[f'{col_letter}{excel_row}']
                cell.fill = fill  # 标色

                comment = Comment(gap_com, '')
                cell.comment = comment

        except Exception as e:
            err_info = f"处理单元格 {col_letter}{excel_row} 时出错: {str(e)}"
            print(err_info)
            error_list.append(err_info)


# 身高
def sg(df, ws, col_name1, col_name2, col_name3, error_list):
    """

    :param df: 预处理表格
    :param ws: 待生成表格
    :param col_name1: 身份证号
    :param col_name2: 性别
    :param col_name3: 身高
    :param error_list: 错误列表
    :return:
    """
    color = rules[col_name3]['不详']['color']
    # 设置单元格背景色
    fill = PatternFill(
        start_color=color,
        end_color=color,
        fill_type='solid'
    )
    gap_com = rules[col_name3]['不详']['comment']
    xs_com = rules[col_name3]['小数']['comment']

    _l1_min = float(rules[col_name3]['儿童']['less1']['min'])
    _l1_max = float(rules[col_name3]['儿童']['less1']['max'])
    _1s_min = float(rules[col_name3]['儿童']['1s']['min'])
    _1s_max = float(rules[col_name3]['儿童']['1s']['max'])
    _2to3_min = float(rules[col_name3]['儿童']['2to3']['min'])
    _2to3_max = float(rules[col_name3]['儿童']['2to3']['max'])
    _4to6_min = float(rules[col_name3]['儿童']['4to6']['min'])
    _4to6_max = float(rules[col_name3]['儿童']['4to6']['max'])
    _7to10_min = float(rules[col_name3]['儿童']['7to10']['min'])
    _7to10_max = float(rules[col_name3]['儿童']['7to10']['max'])
    _11s_min = float(rules[col_name3]['儿童']['11s']['min'])
    _11s_max = float(rules[col_name3]['儿童']['11s']['max'])
    _12s_m_min = float(rules[col_name3]['儿童']['12s']['男']['min'])
    _12s_w_min = float(rules[col_name3]['儿童']['12s']['女']['min'])
    _12s_m_max = float(rules[col_name3]['儿童']['12s']['男']['max'])
    _12s_w_max = float(rules[col_name3]['儿童']['12s']['女']['max'])
    _13s_m_min = float(rules[col_name3]['儿童']['13s']['男']['min'])
    _13s_w_min = float(rules[col_name3]['儿童']['13s']['女']['min'])
    _13s_m_max = float(rules[col_name3]['儿童']['13s']['男']['max'])
    _13s_w_max = float(rules[col_name3]['儿童']['13s']['女']['max'])
    _14s_m_min = float(rules[col_name3]['儿童']['14s']['男']['min'])
    _14s_w_min = float(rules[col_name3]['儿童']['14s']['女']['min'])
    _14s_m_max = float(rules[col_name3]['儿童']['14s']['男']['max'])
    _14s_w_max = float(rules[col_name3]['儿童']['14s']['女']['max'])
    _15s_m_min = float(rules[col_name3]['儿童']['15s']['男']['min'])
    _15s_w_min = float(rules[col_name3]['儿童']['15s']['女']['min'])
    _15s_m_max = float(rules[col_name3]['儿童']['15s']['男']['max'])
    _15s_w_max = float(rules[col_name3]['儿童']['15s']['女']['max'])
    _16s_m_min = float(rules[col_name3]['儿童']['16s']['男']['min'])
    _16s_w_min = float(rules[col_name3]['儿童']['16s']['女']['min'])
    _16s_m_max = float(rules[col_name3]['儿童']['16s']['男']['max'])
    _16s_w_max = float(rules[col_name3]['儿童']['16s']['女']['max'])
    _17s_m_min = float(rules[col_name3]['儿童']['17s']['男']['min'])
    _17s_w_min = float(rules[col_name3]['儿童']['17s']['女']['min'])
    _17s_m_max = float(rules[col_name3]['儿童']['17s']['男']['max'])
    _17s_w_max = float(rules[col_name3]['儿童']['17s']['女']['max'])
    _18s_m_min = float(rules[col_name3]['儿童']['18s']['男']['min'])
    _18s_w_min = float(rules[col_name3]['儿童']['18s']['女']['min'])
    _18s_m_max = float(rules[col_name3]['儿童']['18s']['男']['max'])
    _18s_w_max = float(rules[col_name3]['儿童']['18s']['女']['max'])

    _l1_com = rules[col_name3]['儿童']['less1']['comment']
    _1s_com = rules[col_name3]['儿童']['1s']['comment']
    _2to3_com = rules[col_name3]['儿童']['2to3']['comment']
    _4to6_com = rules[col_name3]['儿童']['4to6']['comment']
    _7to10_com = rules[col_name3]['儿童']['7to10']['comment']
    _11s_com = rules[col_name3]['儿童']['11s']['comment']
    _12s_m_com = rules[col_name3]['儿童']['12s']['男']['comment']
    _12s_w_com = rules[col_name3]['儿童']['12s']['女']['comment']
    _13s_m_com = rules[col_name3]['儿童']['13s']['男']['comment']
    _13s_w_com = rules[col_name3]['儿童']['13s']['女']['comment']
    _14s_m_com = rules[col_name3]['儿童']['14s']['男']['comment']
    _14s_w_com = rules[col_name3]['儿童']['14s']['女']['comment']
    _15s_m_com = rules[col_name3]['儿童']['15s']['男']['comment']
    _15s_w_com = rules[col_name3]['儿童']['15s']['女']['comment']
    _16s_m_com = rules[col_name3]['儿童']['16s']['男']['comment']
    _16s_w_com = rules[col_name3]['儿童']['16s']['女']['comment']
    _17s_m_com = rules[col_name3]['儿童']['17s']['男']['comment']
    _17s_w_com = rules[col_name3]['儿童']['17s']['女']['comment']
    _18s_m_com = rules[col_name3]['儿童']['18s']['男']['comment']
    _18s_w_com = rules[col_name3]['儿童']['18s']['女']['comment']
    col_letter = column_map[col_name3]  # Excel数据列号（字母）

    pattern = r'^[1-9]\d{5}(18|19|20)\d{2}(0[1-9]|1[0-2])(0[1-9]|[12]\d|3[01])\d{3}[\dX]$'
    current_year = datetime.now().year
    # print(type(df.at[0,col_name3]))

    for row_idx in range(handle_num):

        excel_row = row_idx + 2  # Excel数据行号（标题在第1行）
        cell_value = df.at[row_idx, col_name3]
        # print(cell_value)

        try:
            cell = ws[f'{col_letter}{excel_row}']
            if pd.isna(cell_value) or str(cell_value) in ['无', '不详']:
                cell.fill = fill  # 标色
                comment = Comment(gap_com, '')
                cell.comment = comment
            else:  # 有数据的情况下，对儿童的身高进行质控
                cell_value = float(cell_value)
                com_str = ''
                # 先对小数进行判断,向上取整
                has_xs = True if math.ceil(cell_value) > cell_value else False
                if has_xs:
                    cell.fill = fill
                    com_str = com_str + xs_com

                sfzh = df.at[row_idx, col_name1]
                # 当身份证号本身出现问题，则不再进行下一步操作，默认成人->只判断小数点后是否有数
                if not pd.isna(sfzh):
                    sfzh = str(sfzh)
                    if bool(re.match(pattern, sfzh)):
                        birth_year = int(sfzh[6:10])
                        age = current_year - birth_year
                        if 0 <= age < 1:
                            if not _l1_min <= cell_value <= _l1_max:
                                cell.fill = fill  # 标色
                                com_str = com_str + ' ' + _l1_com
                        elif age == 1:
                            if not _1s_min <= cell_value <= _1s_max:
                                cell.fill = fill  # 标色
                                com_str = com_str + ' ' + _1s_com
                        elif 2 <= age <= 3:
                            if not _2to3_min <= cell_value <= _2to3_max:
                                cell.fill = fill  # 标色
                                com_str = com_str + ' ' + _2to3_com
                        elif 4 <= age <= 6:
                            if not _4to6_min <= cell_value <= _4to6_max:
                                cell.fill = fill  # 标色
                                com_str = com_str + ' ' + _4to6_com
                        elif 7 <= age <= 10:
                            if not _7to10_min <= cell_value <= _7to10_max:
                                cell.fill = fill  # 标色
                                com_str = com_str + ' ' + _7to10_com
                        elif age == 11:
                            if not _11s_min <= cell_value <= _11s_max:
                                cell.fill = fill  # 标色
                                com_str = com_str + ' ' + _11s_com
                        elif age == 12:
                            # 性别不详则不进行操作

                            xb = df.at[row_idx, col_name2]
                            if not pd.isna(xb) and xb in ['男', '女']:
                                if xb == '男':
                                    if not _12s_m_min <= cell_value <= _12s_m_max:
                                        cell.fill = fill  # 标色
                                        com_str = com_str + ' ' + _12s_m_com
                                else:
                                    if not _12s_w_min <= cell_value <= _12s_w_max:
                                        cell.fill = fill  # 标色
                                        com_str = com_str + ' ' + _12s_w_com
                        elif age == 13:
                            # 性别不详则不进行操作
                            xb = df.at[row_idx, col_name2]
                            if not pd.isna(xb) and xb in ['男', '女']:
                                if xb == '男':
                                    if not _13s_m_min <= cell_value <= _13s_m_max:
                                        cell.fill = fill  # 标色
                                        com_str = com_str + ' ' + _13s_m_com
                                else:
                                    if not _13s_w_min <= cell_value <= _13s_w_max:
                                        cell.fill = fill  # 标色
                                        com_str = com_str + ' ' + _13s_w_com
                        elif age == 14:
                            # 性别不详则不进行操作
                            xb = df.at[row_idx, col_name2]
                            if not pd.isna(xb) and xb in ['男', '女']:
                                if xb == '男':
                                    if not _14s_m_min <= cell_value <= _14s_m_max:
                                        cell.fill = fill  # 标色
                                        com_str = com_str + ' ' + _14s_m_com
                                else:
                                    if not _14s_w_min <= cell_value <= _14s_w_max:
                                        cell.fill = fill  # 标色
                                        com_str = com_str + ' ' + _14s_w_com
                        elif age == 15:
                            # 性别不详则不进行操作
                            xb = df.at[row_idx, col_name2]
                            if not pd.isna(xb) and xb in ['男', '女']:
                                if xb == '男':
                                    if not _15s_m_min <= cell_value <= _15s_m_max:
                                        cell.fill = fill  # 标色
                                        com_str = com_str + ' ' + _15s_m_com
                                else:
                                    if not _15s_w_min <= cell_value <= _15s_w_max:
                                        cell.fill = fill  # 标色
                                        com_str = com_str + ' ' + _15s_w_com
                        elif age == 16:
                            # 性别不详则不进行操作
                            xb = df.at[row_idx, col_name2]
                            if not pd.isna(xb) and xb in ['男', '女']:
                                if xb == '男':
                                    if not _16s_m_min <= cell_value <= _16s_m_max:
                                        cell.fill = fill  # 标色
                                        com_str = com_str + ' ' + _16s_m_com
                                else:
                                    if not _16s_w_min <= cell_value <= _16s_w_max:
                                        cell.fill = fill  # 标色
                                        com_str = com_str + ' ' + _16s_w_com
                        elif age == 17:
                            # 性别不详则不进行操作
                            xb = df.at[row_idx, col_name2]
                            if not pd.isna(xb) and xb in ['男', '女']:
                                if xb == '男':
                                    if not _17s_m_min <= cell_value <= _17s_m_max:
                                        cell.fill = fill  # 标色
                                        com_str = com_str + ' ' + _17s_m_com
                                else:
                                    if not _17s_w_min <= cell_value <= _17s_w_max:
                                        cell.fill = fill  # 标色
                                        com_str = com_str + ' ' + _17s_w_com
                        elif age == 18:
                            # 性别不详则不进行操作
                            xb = df.at[row_idx, col_name2]
                            if not pd.isna(xb) and xb in ['男', '女']:
                                if xb == '男':
                                    if not _18s_m_min <= cell_value <= _18s_m_max:
                                        cell.fill = fill  # 标色
                                        com_str = com_str + ' ' + _18s_m_com
                                else:
                                    if not _18s_w_min <= cell_value <= _18s_w_max:
                                        cell.fill = fill  # 标色
                                        com_str = com_str + ' ' + _18s_w_com
                        else:
                            pass
                # 总结所有问题,一次性批注
                if com_str != '':
                    comment = Comment(com_str, '')
                    cell.comment = comment

        except Exception as e:
            err_info = f"处理单元格 {col_letter}{excel_row} 时出错: {str(e)}"
            print(err_info)
            error_list.append(err_info)
        cell_f = ws[f"{col_letter}{excel_row}"]
        cell_value_f = cell_f.value
        if cell_value_f != int(cell_value_f):
            cell_f.fill = PatternFill(
                start_color=None,
                end_color=None,
                fill_type=None
            )
            cell_f.comment = None

# 体重
def tz(df, ws, col_name1, col_name2, col_name3, error_list):
    """

    :param df: 预处理表格
    :param ws: 待生成表格
    :param col_name1: 身份证号
    :param col_name2: 性别
    :param col_name3: 体重
    :param error_list: 错误列表
    :return:
    """
    color = rules[col_name3]['不详']['color']
    # 设置单元格背景色
    fill = PatternFill(
        start_color=color,
        end_color=color,
        fill_type='solid'
    )
    gap_com = rules[col_name3]['不详']['comment']
    xs_com = rules[col_name3]['小数']['comment']

    _l1_min = float(rules[col_name3]['儿童']['less1']['min'])
    _l1_max = float(rules[col_name3]['儿童']['less1']['max'])
    _1s_min = float(rules[col_name3]['儿童']['1s']['min'])
    _1s_max = float(rules[col_name3]['儿童']['1s']['max'])
    _2to3_min = float(rules[col_name3]['儿童']['2to3']['min'])
    _2to3_max = float(rules[col_name3]['儿童']['2to3']['max'])
    _4to6_min = float(rules[col_name3]['儿童']['4to6']['min'])
    _4to6_max = float(rules[col_name3]['儿童']['4to6']['max'])
    _7to10_min = float(rules[col_name3]['儿童']['7to10']['min'])
    _7to10_max = float(rules[col_name3]['儿童']['7to10']['max'])
    _11s_min = float(rules[col_name3]['儿童']['11s']['min'])
    _11s_max = float(rules[col_name3]['儿童']['11s']['max'])
    _12s_m_min = float(rules[col_name3]['儿童']['12s']['男']['min'])
    _12s_w_min = float(rules[col_name3]['儿童']['12s']['女']['min'])
    _12s_m_max = float(rules[col_name3]['儿童']['12s']['男']['max'])
    _12s_w_max = float(rules[col_name3]['儿童']['12s']['女']['max'])
    _13s_m_min = float(rules[col_name3]['儿童']['13s']['男']['min'])
    _13s_w_min = float(rules[col_name3]['儿童']['13s']['女']['min'])
    _13s_m_max = float(rules[col_name3]['儿童']['13s']['男']['max'])
    _13s_w_max = float(rules[col_name3]['儿童']['13s']['女']['max'])
    _14s_m_min = float(rules[col_name3]['儿童']['14s']['男']['min'])
    _14s_w_min = float(rules[col_name3]['儿童']['14s']['女']['min'])
    _14s_m_max = float(rules[col_name3]['儿童']['14s']['男']['max'])
    _14s_w_max = float(rules[col_name3]['儿童']['14s']['女']['max'])
    _15s_m_min = float(rules[col_name3]['儿童']['15s']['男']['min'])
    _15s_w_min = float(rules[col_name3]['儿童']['15s']['女']['min'])
    _15s_m_max = float(rules[col_name3]['儿童']['15s']['男']['max'])
    _15s_w_max = float(rules[col_name3]['儿童']['15s']['女']['max'])
    _16s_m_min = float(rules[col_name3]['儿童']['16s']['男']['min'])
    _16s_w_min = float(rules[col_name3]['儿童']['16s']['女']['min'])
    _16s_m_max = float(rules[col_name3]['儿童']['16s']['男']['max'])
    _16s_w_max = float(rules[col_name3]['儿童']['16s']['女']['max'])
    _17s_m_min = float(rules[col_name3]['儿童']['17s']['男']['min'])
    _17s_w_min = float(rules[col_name3]['儿童']['17s']['女']['min'])
    _17s_m_max = float(rules[col_name3]['儿童']['17s']['男']['max'])
    _17s_w_max = float(rules[col_name3]['儿童']['17s']['女']['max'])
    _18s_m_min = float(rules[col_name3]['儿童']['18s']['男']['min'])
    _18s_w_min = float(rules[col_name3]['儿童']['18s']['女']['min'])
    _18s_m_max = float(rules[col_name3]['儿童']['18s']['男']['max'])
    _18s_w_max = float(rules[col_name3]['儿童']['18s']['女']['max'])

    _l1_com = rules[col_name3]['儿童']['less1']['comment']
    _1s_com = rules[col_name3]['儿童']['1s']['comment']
    _2to3_com = rules[col_name3]['儿童']['2to3']['comment']
    _4to6_com = rules[col_name3]['儿童']['4to6']['comment']
    _7to10_com = rules[col_name3]['儿童']['7to10']['comment']
    _11s_com = rules[col_name3]['儿童']['11s']['comment']
    _12s_m_com = rules[col_name3]['儿童']['12s']['男']['comment']
    _12s_w_com = rules[col_name3]['儿童']['12s']['女']['comment']
    _13s_m_com = rules[col_name3]['儿童']['13s']['男']['comment']
    _13s_w_com = rules[col_name3]['儿童']['13s']['女']['comment']
    _14s_m_com = rules[col_name3]['儿童']['14s']['男']['comment']
    _14s_w_com = rules[col_name3]['儿童']['14s']['女']['comment']
    _15s_m_com = rules[col_name3]['儿童']['15s']['男']['comment']
    _15s_w_com = rules[col_name3]['儿童']['15s']['女']['comment']
    _16s_m_com = rules[col_name3]['儿童']['16s']['男']['comment']
    _16s_w_com = rules[col_name3]['儿童']['16s']['女']['comment']
    _17s_m_com = rules[col_name3]['儿童']['17s']['男']['comment']
    _17s_w_com = rules[col_name3]['儿童']['17s']['女']['comment']
    _18s_m_com = rules[col_name3]['儿童']['18s']['男']['comment']
    _18s_w_com = rules[col_name3]['儿童']['18s']['女']['comment']
    col_letter = column_map[col_name3]  # Excel数据列号（字母）

    pattern = r'^[1-9]\d{5}(18|19|20)\d{2}(0[1-9]|1[0-2])(0[1-9]|[12]\d|3[01])\d{3}[\dX]$'
    current_year = datetime.now().year
    # print(type(df.at[0,col_name3]))

    for row_idx in range(handle_num):

        excel_row = row_idx + 2  # Excel数据行号（标题在第1行）
        cell_value = df.at[row_idx, col_name3]
        # print(cell_value)

        try:
            cell = ws[f'{col_letter}{excel_row}']
            if pd.isna(cell_value) or str(cell_value) in ['无', '不详']:
                cell.fill = fill  # 标色
                comment = Comment(gap_com, '')
                cell.comment = comment
            else:
                cell_value = float(cell_value)
                com_str = ''
                # 先对小数进行判断
                has_xs = True if math.ceil(cell_value) > cell_value else False
                if has_xs:
                    cell.fill = fill
                    com_str = com_str + xs_com

                sfzh = df.at[row_idx, col_name1]
                # 当身份证号本身出现问题，则不再进行下一步操作，默认成人->只判断小数点后是否有数
                if not pd.isna(sfzh):
                    sfzh = str(sfzh)
                    if bool(re.match(pattern, sfzh)):
                        birth_year = int(sfzh[6:10])
                        age = current_year - birth_year
                        if 0 <= age < 1:
                            if not _l1_min <= cell_value <= _l1_max:
                                cell.fill = fill  # 标色
                                com_str = com_str + ' ' + _l1_com
                        elif age == 1:
                            if not _1s_min <= cell_value <= _1s_max:
                                cell.fill = fill  # 标色
                                com_str = com_str + ' ' + _1s_com
                        elif 2 <= age <= 3:
                            if not _2to3_min <= cell_value <= _2to3_max:
                                cell.fill = fill  # 标色
                                com_str = com_str + ' ' + _2to3_com
                        elif 4 <= age <= 6:
                            if not _4to6_min <= cell_value <= _4to6_max:
                                cell.fill = fill  # 标色
                                com_str = com_str + ' ' + _4to6_com
                        elif 7 <= age <= 10:
                            if not _7to10_min <= cell_value <= _7to10_max:
                                cell.fill = fill  # 标色
                                com_str = com_str + ' ' + _7to10_com
                        elif age == 11:
                            if not _11s_min <= cell_value <= _11s_max:
                                cell.fill = fill  # 标色
                                com_str = com_str + ' ' + _11s_com
                        elif age == 12:
                            # 性别不详则不进行操作
                            xb = df.at[row_idx, col_name2]
                            if not pd.isna(xb) and xb in ['男', '女']:
                                if xb == '男':
                                    if not _12s_m_min <= cell_value <= _12s_m_max:
                                        cell.fill = fill  # 标色
                                        com_str = com_str + ' ' + _12s_m_com
                                else:
                                    if not _12s_w_min <= cell_value <= _12s_w_max:
                                        cell.fill = fill  # 标色
                                        com_str = com_str + ' ' + _12s_w_com
                        elif age == 13:
                            # 性别不详则不进行操作
                            xb = df.at[row_idx, col_name2]
                            if not pd.isna(xb) and xb in ['男', '女']:
                                if xb == '男':
                                    if not _13s_m_min <= cell_value <= _13s_m_max:
                                        cell.fill = fill  # 标色
                                        com_str = com_str + ' ' + _13s_m_com
                                else:
                                    if not _13s_w_min <= cell_value <= _13s_w_max:
                                        cell.fill = fill  # 标色
                                        com_str = com_str + ' ' + _13s_w_com
                        elif age == 14:
                            # 性别不详则不进行操作
                            xb = df.at[row_idx, col_name2]
                            if not pd.isna(xb) and xb in ['男', '女']:
                                if xb == '男':
                                    if not _14s_m_min <= cell_value <= _14s_m_max:
                                        cell.fill = fill  # 标色
                                        com_str = com_str + ' ' + _14s_m_com
                                else:
                                    if not _14s_w_min <= cell_value <= _14s_w_max:
                                        cell.fill = fill  # 标色
                                        com_str = com_str + ' ' + _14s_w_com
                        elif age == 15:
                            # 性别不详则不进行操作
                            xb = df.at[row_idx, col_name2]
                            if not pd.isna(xb) and xb in ['男', '女']:
                                if xb == '男':
                                    if not _15s_m_min <= cell_value <= _15s_m_max:
                                        cell.fill = fill  # 标色
                                        com_str = com_str + ' ' + _15s_m_com
                                else:
                                    if not _15s_w_min <= cell_value <= _15s_w_max:
                                        cell.fill = fill  # 标色
                                        com_str = com_str + ' ' + _15s_w_com
                        elif age == 16:
                            # 性别不详则不进行操作
                            xb = df.at[row_idx, col_name2]
                            if not pd.isna(xb) and xb in ['男', '女']:
                                if xb == '男':
                                    if not _16s_m_min <= cell_value <= _16s_m_max:
                                        cell.fill = fill  # 标色
                                        com_str = com_str + ' ' + _16s_m_com
                                else:
                                    if not _16s_w_min <= cell_value <= _16s_w_max:
                                        cell.fill = fill  # 标色
                                        com_str = com_str + ' ' + _16s_w_com
                        elif age == 17:
                            # 性别不详则不进行操作
                            xb = df.at[row_idx, col_name2]
                            if not pd.isna(xb) and xb in ['男', '女']:
                                if xb == '男':
                                    if not _17s_m_min <= cell_value <= _17s_m_max:
                                        cell.fill = fill  # 标色
                                        com_str = com_str + ' ' + _17s_m_com
                                else:
                                    if not _17s_w_min <= cell_value <= _17s_w_max:
                                        cell.fill = fill  # 标色
                                        com_str = com_str + ' ' + _17s_w_com
                        elif age == 18:
                            # 性别不详则不进行操作
                            xb = df.at[row_idx, col_name2]
                            if not pd.isna(xb) and xb in ['男', '女']:
                                if xb == '男':
                                    if not _18s_m_min <= cell_value <= _18s_m_max:
                                        cell.fill = fill  # 标色
                                        com_str = com_str + ' ' + _18s_m_com
                                else:
                                    if not _18s_w_min <= cell_value <= _18s_w_max:
                                        cell.fill = fill  # 标色
                                        com_str = com_str + ' ' + _18s_w_com
                        else:
                            pass
                # 总结所有问题,一次性批注
                if com_str != '':
                    comment = Comment(com_str, '')
                    cell.comment = comment

        except Exception as e:
            err_info = f"处理单元格 {col_letter}{excel_row} 时出错: {str(e)}"
            print(err_info)
            error_list.append(err_info)
        cell_f = ws[f"{col_letter}{excel_row}"]
        cell_value_f = cell_f.value
        if cell_value_f != int(cell_value_f):
            cell_f.fill = PatternFill(
                start_color=None,
                end_color=None,
                fill_type=None
            )
            cell_f.comment = None

# 腰围
def yw(df, ws, col_name, error_list):
    """

    :param df: 预处理表格
    :param ws: 待生成表格
    :param col_name: 腰围
    :param error_list: 错误列表
    :return:
    """
    color = rules[col_name]['不详']['color']
    # 设置单元格背景色
    fill = PatternFill(
        start_color=color,
        end_color=color,
        fill_type='solid'
    )
    gap_com = rules[col_name]['不详']['comment']
    xs_com = rules[col_name]['小数']['comment']
    col_letter = column_map[col_name]

    for row_idx in range(handle_num):

        excel_row = row_idx + 2  # Excel数据行号（标题在第1行）
        cell_value = df.at[row_idx, col_name]
        # print(cell_value)

        try:
            if pd.isna(cell_value) or str(cell_value) in ['无', '不详']:
                cell = ws[f'{col_letter}{excel_row}']
                cell.fill = fill  # 标色
                comment = Comment(gap_com, '')
                cell.comment = comment
            else:
                cell_value = float(cell_value)
                # 检测小数
                has_xs = True if math.ceil(cell_value) > cell_value else False
                if has_xs:
                    cell = ws[f'{col_letter}{excel_row}']
                    cell.fill = fill  # 标色

                    comment = Comment(xs_com, '')
                    cell.comment = comment
        except Exception as e:
            err_info = f"处理单元格 {col_letter}{excel_row} 时出错: {str(e)}"
            print(err_info)
            error_list.append(err_info)


# 臀围
def tw(df, ws, col_name, error_list):
    """

    :param df: 预处理表格
    :param ws: 待生成表格
    :param col_name: 臀围
    :param error_list: 错误列表
    :return:
    """
    color = rules[col_name]['不详']['color']
    # 设置单元格背景色
    fill = PatternFill(
        start_color=color,
        end_color=color,
        fill_type='solid'
    )
    gap_com = rules[col_name]['不详']['comment']
    xs_com = rules[col_name]['小数']['comment']
    col_letter = column_map[col_name]
    for row_idx in range(handle_num):

        excel_row = row_idx + 2  # Excel数据行号（标题在第1行）
        cell_value = df.at[row_idx, col_name]
        # print(cell_value)

        try:
            if pd.isna(cell_value) or str(cell_value) in ['无', '不详']:
                cell = ws[f'{col_letter}{excel_row}']
                cell.fill = fill  # 标色
                comment = Comment(gap_com, '')
                cell.comment = comment
            else:
                cell_value = float(cell_value)
                # 检测小数
                has_xs = True if math.ceil(cell_value) > cell_value else False
                if has_xs:
                    cell = ws[f'{col_letter}{excel_row}']
                    cell.fill = fill  # 标色

                    comment = Comment(xs_com, '')
                    cell.comment = comment
        except Exception as e:
            err_info = f"处理单元格 {col_letter}{excel_row} 时出错: {str(e)}"
            print(err_info)
            error_list.append(err_info)


# 吸烟
def xy(df, ws, col_name1, col_name2, col_name3, col_name4, col_name5, error_list):
    """

    :param df: 预处理表格
    :param ws: 待生成表格
    :param col_name1: 身份证号
    :param col_name2: 是否吸烟
    :param col_name3: 开始吸烟时间
    :param col_name4: 戒断时间
    :param col_name5: 吸烟量
    :param error_list: 错误列表
    :return:
    """
    color = rules[col_name2]['不详']['color']
    # 设置单元格背景色
    fill = PatternFill(
        start_color=color,
        end_color=color,
        fill_type='solid'
    )
    gap_com = rules[col_name2]['不详']['comment']
    child_com = rules[col_name2]['儿童']['comment']
    y_gap_com = rules[col_name2]['不匹配']['是']['comment1']
    y_wu_com = rules[col_name2]['不匹配']['是']['comment2']
    y_chi_com = rules[col_name2]['不匹配']['是']['comment3']
    n_wrong_com = rules[col_name2]['不匹配']['否']['comment']
    yj_gap_com = rules[col_name2]['不匹配']['已戒']['comment1']
    yj_chi_com = rules[col_name2]['不匹配']['已戒']['comment2']
    yj_day_com = rules[col_name2]['不匹配']['已戒']['comment3']
    col_letter = column_map[col_name2]  # 是否吸烟
    col_letter1 = column_map[col_name3]  # 开始吸烟时间
    col_letter2 = column_map[col_name4]  # 戒断时间
    col_letter3 = column_map[col_name5]  # 吸烟量

    pattern = r'^[1-9]\d{5}(18|19|20)\d{2}(0[1-9]|1[0-2])(0[1-9]|[12]\d|3[01])\d{3}[\dX]$'
    current_year = datetime.now().year

    for row_idx in range(handle_num):

        excel_row = row_idx + 2  # Excel数据行号（标题在第1行）
        cell_value = df.at[row_idx, col_name2]
        cell_value1 = df.at[row_idx, col_name3]  # 开始吸烟时间
        cell_value2 = df.at[row_idx, col_name4]  # 戒断时间
        cell_value3 = df.at[row_idx, col_name5]  # 吸烟量

        # print(cell_value)

        try:
            # 这边的逻辑是:如果"是否吸烟"为空白,则不再进行后续操作
            if pd.isna(cell_value):
                cell = ws[f'{col_letter}{excel_row}']
                cell.fill = fill  # 标色
                comment = Comment(gap_com, '')
                cell.comment = comment
            else:
                if cell_value == '无':  # ‘无’的处理和‘否’应该是一样的
                    # 开始吸烟时间
                    if pd.isna(cell_value1):
                        cell1 = ws[f'{col_letter1}{excel_row}']
                        cell1.fill = fill
                        comment = Comment(gap_com, '')
                        cell1.comment = comment
                    else:
                        if str(cell_value1) != '无':
                            cell1 = ws[f'{col_letter1}{excel_row}']
                            cell1.fill = fill
                            comment = Comment(n_wrong_com, '')
                            cell1.comment = comment
                    # 戒断时间
                    if pd.isna(cell_value2):
                        cell2 = ws[f'{col_letter2}{excel_row}']
                        cell2.fill = fill
                        comment = Comment(gap_com, '')
                        cell2.comment = comment
                    else:
                        if str(cell_value2) != '无':
                            cell2 = ws[f'{col_letter2}{excel_row}']
                            cell2.fill = fill
                            comment = Comment(n_wrong_com, '')
                            cell2.comment = comment
                    # 吸烟量
                    if pd.isna(cell_value3):
                        cell3 = ws[f'{col_letter3}{excel_row}']
                        cell3.fill = fill
                        comment = Comment(gap_com, '')
                        cell3.comment = comment
                    else:
                        if str(cell_value3) != '无':
                            cell3 = ws[f'{col_letter3}{excel_row}']
                            cell3.fill = fill
                            comment = Comment(n_wrong_com, '')
                            cell3.comment = comment
                else:
                    sfzh = df.at[row_idx, col_name1]
                    if not pd.isna(sfzh):
                        """
                        先计算年龄,若因身份证号有误导致年龄无法计算则不予处理;若年龄可以计算,对于儿童一律填无;若年龄可以计算,对于成人来说与出生日期比较
                        """
                        sfzh = str(sfzh)
                        if bool(re.match(pattern, sfzh)):
                            birth_year = int(sfzh[6:10])
                            age = current_year - birth_year
                            if 0 <= age <= 18:
                                # # 是否吸烟
                                # if cell_value == '是':
                                #     cell = ws[f'{col_letter}{excel_row}']
                                #     cell.fill = fill  # 标色
                                #     comment = Comment(child_com, '')
                                #     cell.comment = comment
                                # 开始吸烟时间
                                if pd.isna(cell_value1):
                                    cell1 = ws[f'{col_letter1}{excel_row}']
                                    cell1.fill = fill
                                    comment = Comment(gap_com, '')
                                    cell1.comment = comment
                                else:
                                    if str(cell_value1) != '无':
                                        cell1 = ws[f'{col_letter1}{excel_row}']
                                        cell1.fill = fill
                                        comment = Comment(child_com, '')
                                        cell1.comment = comment
                                # 戒断时间
                                if pd.isna(cell_value2):
                                    cell2 = ws[f'{col_letter2}{excel_row}']
                                    cell2.fill = fill
                                    comment = Comment(gap_com, '')
                                    cell2.comment = comment
                                else:
                                    if str(cell_value2) != '无':
                                        cell2 = ws[f'{col_letter2}{excel_row}']
                                        cell2.fill = fill
                                        comment = Comment(child_com, '')
                                        cell2.comment = comment
                                # 吸烟量
                                if pd.isna(cell_value3):
                                    cell3 = ws[f'{col_letter3}{excel_row}']
                                    cell3.fill = fill
                                    comment = Comment(gap_com, '')
                                    cell3.comment = comment
                                else:
                                    if cell_value3 != '无':
                                        cell3 = ws[f'{col_letter3}{excel_row}']
                                        cell3.fill = fill
                                        comment = Comment(child_com, '')
                                        cell3.comment = comment
                            else:
                                # 成人无需处理是否吸烟,我们只对时间进行判断
                                # 首先判断是否吸烟,若为否则后面的几个字段应为"无"
                                if cell_value == '否':
                                    # 开始吸烟时间
                                    if pd.isna(cell_value1):
                                        cell1 = ws[f'{col_letter1}{excel_row}']
                                        cell1.fill = fill
                                        comment = Comment(gap_com, '')
                                        cell1.comment = comment
                                    else:
                                        if str(cell_value1) != '无':
                                            cell1 = ws[f'{col_letter1}{excel_row}']
                                            cell1.fill = fill
                                            comment = Comment(n_wrong_com, '')
                                            cell1.comment = comment
                                    # 戒断时间
                                    if pd.isna(cell_value2):
                                        cell2 = ws[f'{col_letter2}{excel_row}']
                                        cell2.fill = fill
                                        comment = Comment(gap_com, '')
                                        cell2.comment = comment
                                    else:
                                        if str(cell_value2) != '无':
                                            cell2 = ws[f'{col_letter2}{excel_row}']
                                            cell2.fill = fill
                                            comment = Comment(n_wrong_com, '')
                                            cell2.comment = comment
                                    # 吸烟量
                                    if pd.isna(cell_value3):
                                        cell3 = ws[f'{col_letter3}{excel_row}']
                                        cell3.fill = fill
                                        comment = Comment(gap_com, '')
                                        cell3.comment = comment
                                    else:
                                        if str(cell_value3) != '无':
                                            cell3 = ws[f'{col_letter3}{excel_row}']
                                            cell3.fill = fill
                                            comment = Comment(n_wrong_com, '')
                                            cell3.comment = comment
                                elif cell_value == '已戒':
                                    # 对于戒烟人群,首先与出生日期比较,其次吸烟和戒烟日期再比较
                                    if pd.isna(cell_value1):
                                        cell1 = ws[f'{col_letter1}{excel_row}']
                                        cell1.fill = fill
                                        comment = Comment(gap_com, '')
                                        cell1.comment = comment
                                    else:
                                        if str(cell_value1) == '无':
                                            cell1 = ws[f'{col_letter1}{excel_row}']
                                            cell1.fill = fill
                                            comment = Comment(yj_gap_com, '')
                                            cell1.comment = comment
                                        # else:
                                        #     if int(cell_value1) - birth_year < 18:
                                        #         cell1 = ws[f'{col_letter1}{excel_row}']
                                        #         cell1.fill = fill
                                        #         comment = Comment(yj_chi_com, '')
                                        #         cell1.comment = comment
                                    if pd.isna(cell_value2):
                                        cell2 = ws[f'{col_letter2}{excel_row}']
                                        cell2.fill = fill
                                        comment = Comment(gap_com, '')
                                        cell2.comment = comment
                                    else:
                                        if str(cell_value2) == '无':
                                            cell2 = ws[f'{col_letter2}{excel_row}']
                                            cell2.fill = fill
                                            comment = Comment(yj_gap_com, '')
                                            cell2.comment = comment
                                        else:
                                            com_ = ''
                                            # if int(cell_value2) - birth_year < 18:
                                            #     cell2 = ws[f'{col_letter2}{excel_row}']
                                            #     cell2.fill = fill
                                            #     com_ += yj_chi_com
                                            if not pd.isna(cell_value1) and str(cell_value1) != '无':
                                                if int(cell_value2) - int(cell_value1) < 0:
                                                    cell2 = ws[f'{col_letter2}{excel_row}']
                                                    cell2.fill = fill
                                                    if com_ != '':
                                                        com_ += ','
                                                    com_ += yj_day_com
                                            if com_ != '':
                                                cell2 = ws[f'{col_letter2}{excel_row}']
                                                comment = Comment(com_, '')
                                                cell2.comment = comment
                                    if pd.isna(cell_value3):
                                        cell3 = ws[f'{col_letter3}{excel_row}']
                                        cell3.fill = fill
                                        comment = Comment(gap_com, '')
                                        cell3.comment = comment
                                    else:
                                        if cell_value3 == '无':
                                            cell3 = ws[f'{col_letter3}{excel_row}']
                                            cell3.fill = fill
                                            comment = Comment(yj_gap_com, '')
                                            cell3.comment = comment
                                elif cell_value == '是':
                                    # 需判断开始的时间,并且确保戒断时间是无
                                    if pd.isna(cell_value1):
                                        cell1 = ws[f'{col_letter1}{excel_row}']
                                        cell1.fill = fill
                                        comment = Comment(gap_com, '')
                                        cell1.comment = comment
                                    else:
                                        if str(cell_value1) == '无':
                                            cell1 = ws[f'{col_letter1}{excel_row}']
                                            cell1.fill = fill
                                            comment = Comment(y_gap_com, '')
                                            cell1.comment = comment
                                        # else:
                                        #     if int(cell_value1) - birth_year < 18:
                                        #         cell1 = ws[f'{col_letter1}{excel_row}']
                                        #         cell1.fill = fill
                                        #         comment = Comment(y_chi_com, '')
                                        #         cell1.comment = comment
                                    if pd.isna(cell_value2):
                                        cell2 = ws[f'{col_letter2}{excel_row}']
                                        cell2.fill = fill
                                        comment = Comment(gap_com, '')
                                        cell2.comment = comment
                                    else:
                                        if str(cell_value2) != '无':
                                            cell2 = ws[f'{col_letter2}{excel_row}']
                                            cell2.fill = fill
                                            comment = Comment(y_wu_com, '')
                                            cell2.comment = comment
                                    if pd.isna(cell_value3):
                                        cell3 = ws[f'{col_letter3}{excel_row}']
                                        cell3.fill = fill
                                        comment = Comment(gap_com, '')
                                        cell3.comment = comment
                                    else:
                                        if cell_value3 == '无':
                                            cell3 = ws[f'{col_letter3}{excel_row}']
                                            cell3.fill = fill
                                            comment = Comment(y_gap_com, '')
                                            cell3.comment = comment
                                else:
                                    pass

        except Exception as e:
            err_info = f"处理单元格 {col_letter}{excel_row} 时出错: {str(e)}"
            print(err_info)
            error_list.append(err_info)


# 饮酒
def yj(df, ws, col_name1, col_name2, col_name3, col_name4, col_name5, col_name6, error_list):
    """

    :param df: 预处理表格
    :param ws: 待生成表格
    :param col_name1: 身份证号
    :param col_name2: 是否饮酒
    :param col_name3: 饮酒类型
    :param col_name4: 饮酒量
    :param col_name5: 饮酒频率
    :param col_name6: 戒酒日期
    :param error_list: 错误列表
    :return:
    """
    color = rules[col_name2]['不详']['color']
    # 设置单元格背景色
    fill = PatternFill(
        start_color=color,
        end_color=color,
        fill_type='solid'
    )
    gap_com = rules[col_name2]['不详']['comment']
    child_com = rules[col_name2]['儿童']['comment']
    y_gap_com = rules[col_name2]['不匹配']['是']['comment1']
    y_wu_com = rules[col_name2]['不匹配']['是']['comment2']
    n_wrong_com = rules[col_name2]['不匹配']['否']['comment']
    yj_gap_com = rules[col_name2]['不匹配']['已戒']['comment1']
    yj_chi_com = rules[col_name2]['不匹配']['已戒']['comment2']
    col_letter = column_map[col_name2]  # 是否饮酒
    col_letter1 = column_map[col_name3]  # 饮酒类型
    col_letter2 = column_map[col_name4]  # 饮酒量
    col_letter3 = column_map[col_name5]  # 饮酒频率
    col_letter4 = column_map[col_name6]  # 戒酒日期

    pattern = r'^[1-9]\d{5}(18|19|20)\d{2}(0[1-9]|1[0-2])(0[1-9]|[12]\d|3[01])\d{3}[\dX]$'
    current_year = datetime.now().year

    for row_idx in range(handle_num):

        excel_row = row_idx + 2  # Excel数据行号（标题在第1行）
        cell_value = df.at[row_idx, col_name2]
        cell_value1 = df.at[row_idx, col_name3]  # 饮酒类型
        cell_value2 = df.at[row_idx, col_name4]  # 饮酒量
        cell_value3 = df.at[row_idx, col_name5]  # 饮酒频率
        cell_value4 = df.at[row_idx, col_name6]  # 戒酒日期,默认是4位的年份
        # print(cell_value)

        try:
            # 这边的逻辑是:如果"是否饮酒"为空白,则不再进行后续操作
            if pd.isna(cell_value):
                cell = ws[f'{col_letter}{excel_row}']
                cell.fill = fill  # 标色

                comment = Comment(gap_com, '')
                cell.comment = comment
            else:
                if cell_value == '无':
                    # 饮酒类型
                    if pd.isna(cell_value1):
                        cell1 = ws[f'{col_letter1}{excel_row}']
                        cell1.fill = fill
                        comment = Comment(gap_com, '')
                        cell1.comment = comment
                    else:
                        if cell_value1 != '无':
                            cell1 = ws[f'{col_letter1}{excel_row}']
                            cell1.fill = fill
                            comment = Comment(n_wrong_com, '')
                            cell1.comment = comment
                    # 饮酒量
                    if pd.isna(cell_value2):
                        cell2 = ws[f'{col_letter2}{excel_row}']
                        cell2.fill = fill
                        comment = Comment(gap_com, '')
                        cell2.comment = comment
                    else:
                        if cell_value2 != '无':
                            cell2 = ws[f'{col_letter2}{excel_row}']
                            cell2.fill = fill
                            comment = Comment(n_wrong_com, '')
                            cell2.comment = comment
                    # 饮酒频率
                    if pd.isna(cell_value3):
                        cell3 = ws[f'{col_letter3}{excel_row}']
                        cell3.fill = fill
                        comment = Comment(gap_com, '')
                        cell3.comment = comment
                    else:
                        if cell_value3 != '无':
                            cell3 = ws[f'{col_letter3}{excel_row}']
                            cell3.fill = fill
                            comment = Comment(n_wrong_com, '')
                            cell3.comment = comment
                    # 戒酒日期
                    if pd.isna(cell_value4):
                        cell4 = ws[f'{col_letter4}{excel_row}']
                        cell4.fill = fill
                        comment = Comment(gap_com, '')
                        cell4.comment = comment
                    else:
                        if str(cell_value4) != '无':
                            cell4 = ws[f'{col_letter4}{excel_row}']
                            cell4.fill = fill
                            comment = Comment(n_wrong_com, '')
                            cell4.comment = comment
                # 身份证号不详则不进行后续操作,因为判断情况受身份证号影响
                else:
                    sfzh = df.at[row_idx, col_name1]
                    if not pd.isna(sfzh):
                        sfzh = str(sfzh)
                        if bool(re.match(pattern, sfzh)):
                            birth_year = int(sfzh[6:10])
                            age = current_year - birth_year
                            if 0 <= age <= 18:
                                # 是否饮酒
                                if cell_value == '是':
                                    cell = ws[f'{col_letter}{excel_row}']
                                    cell.fill = fill  # 标色

                                    comment = Comment(child_com, '')
                                    cell.comment = comment
                                # 饮酒类型
                                if pd.isna(cell_value1):
                                    cell1 = ws[f'{col_letter1}{excel_row}']
                                    cell1.fill = fill

                                    comment = Comment(gap_com, '')
                                    cell1.comment = comment
                                else:
                                    if cell_value1 != '无':
                                        cell1 = ws[f'{col_letter1}{excel_row}']
                                        cell1.fill = fill

                                        comment = Comment(child_com, '')
                                        cell1.comment = comment
                                # 饮酒量
                                if pd.isna(cell_value2):
                                    cell2 = ws[f'{col_letter2}{excel_row}']
                                    cell2.fill = fill

                                    comment = Comment(gap_com, '')
                                    cell2.comment = comment
                                else:
                                    if cell_value2 != '无':
                                        cell2 = ws[f'{col_letter2}{excel_row}']
                                        cell2.fill = fill

                                        comment = Comment(child_com, '')
                                        cell2.comment = comment
                                # 饮酒频率
                                if pd.isna(cell_value3):
                                    cell3 = ws[f'{col_letter3}{excel_row}']
                                    cell3.fill = fill

                                    comment = Comment(gap_com, '')
                                    cell3.comment = comment
                                else:
                                    if cell_value3 != '无':
                                        cell3 = ws[f'{col_letter3}{excel_row}']
                                        cell3.fill = fill

                                        comment = Comment(child_com, '')
                                        cell3.comment = comment
                                # 饮酒日期
                                if pd.isna(cell_value4):
                                    cell4 = ws[f'{col_letter4}{excel_row}']
                                    cell4.fill = fill

                                    comment = Comment(gap_com, '')
                                    cell4.comment = comment
                                else:
                                    if str(cell_value4) != '无':
                                        cell4 = ws[f'{col_letter4}{excel_row}']
                                        cell4.fill = fill

                                        comment = Comment(child_com, '')
                                        cell4.comment = comment
                            else:
                                # 成人首先判断是否饮酒,若为否则后面的几个字段应为"无"
                                if cell_value == '否':
                                    # 饮酒类型
                                    if pd.isna(cell_value1):
                                        cell1 = ws[f'{col_letter1}{excel_row}']
                                        cell1.fill = fill

                                        comment = Comment(gap_com, '')
                                        cell1.comment = comment
                                    else:
                                        if cell_value1 != '无':
                                            cell1 = ws[f'{col_letter1}{excel_row}']
                                            cell1.fill = fill

                                            comment = Comment(n_wrong_com, '')
                                            cell1.comment = comment
                                    # 饮酒量
                                    if pd.isna(cell_value2):
                                        cell2 = ws[f'{col_letter2}{excel_row}']
                                        cell2.fill = fill

                                        comment = Comment(gap_com, '')
                                        cell2.comment = comment
                                    else:
                                        if cell_value2 != '无':
                                            cell2 = ws[f'{col_letter2}{excel_row}']
                                            cell2.fill = fill

                                            comment = Comment(n_wrong_com, '')
                                            cell2.comment = comment
                                    # 饮酒频率
                                    if pd.isna(cell_value3):
                                        cell3 = ws[f'{col_letter3}{excel_row}']
                                        cell3.fill = fill

                                        comment = Comment(gap_com, '')
                                        cell3.comment = comment
                                    else:
                                        if cell_value3 != '无':
                                            cell3 = ws[f'{col_letter3}{excel_row}']
                                            cell3.fill = fill

                                            comment = Comment(n_wrong_com, '')
                                            cell3.comment = comment
                                    # 戒酒日期
                                    if pd.isna(cell_value4):
                                        cell4 = ws[f'{col_letter4}{excel_row}']
                                        cell4.fill = fill

                                        comment = Comment(gap_com, '')
                                        cell4.comment = comment
                                    else:
                                        if str(cell_value4) != '无':
                                            cell4 = ws[f'{col_letter4}{excel_row}']
                                            cell4.fill = fill

                                            comment = Comment(n_wrong_com, '')
                                            cell4.comment = comment

                                elif cell_value == '已戒':

                                    if pd.isna(cell_value1):
                                        cell1 = ws[f'{col_letter1}{excel_row}']
                                        cell1.fill = fill
                                        comment = Comment(gap_com, '')
                                        cell1.comment = comment
                                    else:
                                        if cell_value1 == '无':
                                            cell1 = ws[f'{col_letter1}{excel_row}']
                                            cell1.fill = fill
                                            comment = Comment(yj_gap_com, '')
                                            cell1.comment = comment
                                    if pd.isna(cell_value2):
                                        cell2 = ws[f'{col_letter2}{excel_row}']
                                        cell2.fill = fill
                                        comment = Comment(gap_com, '')
                                        cell2.comment = comment
                                    else:
                                        if cell_value2 == '无':
                                            cell2 = ws[f'{col_letter2}{excel_row}']
                                            cell2.fill = fill
                                            comment = Comment(yj_gap_com, '')
                                            cell2.comment = comment
                                    if pd.isna(cell_value3):
                                        cell3 = ws[f'{col_letter3}{excel_row}']
                                        cell3.fill = fill
                                        comment = Comment(gap_com, '')
                                        cell3.comment = comment
                                    else:
                                        if cell_value3 == '无':
                                            cell3 = ws[f'{col_letter3}{excel_row}']
                                            cell3.fill = fill
                                            comment = Comment(yj_gap_com, '')
                                            cell3.comment = comment
                                    if pd.isna(cell_value4):
                                        cell4 = ws[f'{col_letter4}{excel_row}']
                                        cell4.fill = fill
                                        comment = Comment(gap_com, '')
                                        cell4.comment = comment
                                    else:
                                        if cell_value4 == '无':
                                            cell4 = ws[f'{col_letter4}{excel_row}']
                                            cell4.fill = fill
                                            comment = Comment(yj_gap_com, '')
                                            cell4.comment = comment
                                        # else:
                                        #     if int(str(cell_value4)[:4]) - birth_year < 18:
                                        #         cell4 = ws[f'{col_letter4}{excel_row}']
                                        #         cell4.fill = fill
                                        #         comment = Comment(yj_chi_com, '')
                                        #         cell4.comment = comment

                                elif cell_value == '是':
                                    if pd.isna(cell_value1):
                                        cell1 = ws[f'{col_letter1}{excel_row}']
                                        cell1.fill = fill
                                        comment = Comment(gap_com, '')
                                        cell1.comment = comment
                                    else:
                                        if cell_value1 == '无':
                                            cell1 = ws[f'{col_letter1}{excel_row}']
                                            cell1.fill = fill
                                            comment = Comment(y_gap_com, '')
                                            cell1.comment = comment
                                    if pd.isna(cell_value2):
                                        cell2 = ws[f'{col_letter2}{excel_row}']
                                        cell2.fill = fill
                                        comment = Comment(gap_com, '')
                                        cell2.comment = comment
                                    else:
                                        if cell_value2 == '无':
                                            cell2 = ws[f'{col_letter2}{excel_row}']
                                            cell2.fill = fill
                                            comment = Comment(y_gap_com, '')
                                            cell2.comment = comment
                                    if pd.isna(cell_value3):
                                        cell3 = ws[f'{col_letter3}{excel_row}']
                                        cell3.fill = fill
                                        comment = Comment(gap_com, '')
                                        cell3.comment = comment
                                    else:
                                        if cell_value3 == '无':
                                            cell3 = ws[f'{col_letter3}{excel_row}']
                                            cell3.fill = fill
                                            comment = Comment(y_gap_com, '')
                                            cell3.comment = comment
                                    if pd.isna(cell_value4):
                                        cell4 = ws[f'{col_letter4}{excel_row}']
                                        cell4.fill = fill
                                        comment = Comment(gap_com, '')
                                        cell4.comment = comment
                                    else:  # 没有戒酒此字段应为无
                                        if cell_value4 != '无':
                                            cell4 = ws[f'{col_letter4}{excel_row}']
                                            cell4.fill = fill
                                            comment = Comment(y_wu_com, '')
                                            cell4.comment = comment
                                else:
                                    pass

        except Exception as e:
            err_info = f"处理单元格 {col_letter}{excel_row} 时出错: {str(e)}"
            print(err_info)
            error_list.append(err_info)


# 锻炼
def dl(df, ws, col_name1, col_name2, col_name3, col_name4, error_list):
    """

    :param df: 预处理表格
    :param ws: 待生成表格
    :param col_name1: 是否锻炼
    :param col_name2: 锻炼情况
    :param col_name3: 每次锻炼时间
    :param col_name4: 锻炼类型
    :param error_list: 错误列表
    :return:
    """
    color = rules[col_name1]['不详']['color']
    # 设置单元格背景色
    fill = PatternFill(
        start_color=color,
        end_color=color,
        fill_type='solid'
    )
    gap_com = rules[col_name1]['不详']['comment']
    y_qk_com = rules[col_name1]['是']['锻炼情况']['comment']
    y_sj_com = rules[col_name1]['是']['每次锻炼时间']['comment']
    y_lx_com = rules[col_name1]['是']['锻炼类型']['comment']
    n_qk_com = rules[col_name1]['否']['锻炼情况']['comment']
    n_sj_com = rules[col_name1]['否']['每次锻炼时间']['comment']
    n_lx_com = rules[col_name1]['否']['锻炼类型']['comment']
    col_letter = column_map[col_name1]  # 是否饮酒
    col_letter1 = column_map[col_name2]  # 锻炼情况
    col_letter2 = column_map[col_name3]  # 每次锻炼时间
    col_letter3 = column_map[col_name4]  # 锻炼类型

    for row_idx in range(handle_num):

        excel_row = row_idx + 2  # Excel数据行号（标题在第1行）
        cell_value = df.at[row_idx, col_name1]
        cell_value1 = df.at[row_idx, col_name2]
        cell_value2 = df.at[row_idx, col_name3]
        cell_value3 = df.at[row_idx, col_name4]
        # print(cell_value)

        try:
            # 是否锻炼为空则不进行后续处理
            if pd.isna(cell_value):
                cell = ws[f'{col_letter}{excel_row}']
                cell.fill = fill  # 标色
                comment = Comment(gap_com, '')
                cell.comment = comment
            else:
                if cell_value == '是':
                    if pd.isna(cell_value1):
                        cell1 = ws[f'{col_letter1}{excel_row}']
                        cell1.fill = fill  # 标色
                        comment = Comment(gap_com, '')
                        cell1.comment = comment
                    else:
                        if cell_value1 == '无':
                            cell1 = ws[f'{col_letter1}{excel_row}']
                            cell1.fill = fill  # 标色
                            comment = Comment(y_qk_com, '')
                            cell1.comment = comment
                    if pd.isna(cell_value2):
                        cell2 = ws[f'{col_letter2}{excel_row}']
                        cell2.fill = fill  # 标色
                        comment = Comment(gap_com, '')
                        cell2.comment = comment
                    else:
                        if cell_value2 == '无':
                            cell2 = ws[f'{col_letter2}{excel_row}']
                            cell2.fill = fill  # 标色
                            comment = Comment(y_sj_com, '')
                            cell2.comment = comment
                    if pd.isna(cell_value3):
                        cell3 = ws[f'{col_letter3}{excel_row}']
                        cell3.fill = fill  # 标色
                        comment = Comment(gap_com, '')
                        cell3.comment = comment
                    else:
                        if cell_value3 == '无':
                            cell3 = ws[f'{col_letter3}{excel_row}']
                            cell3.fill = fill  # 标色
                            comment = Comment(y_lx_com, '')
                            cell3.comment = comment
                elif cell_value in ['否', '无规律', '无']:
                    if pd.isna(cell_value1):
                        cell1 = ws[f'{col_letter1}{excel_row}']
                        cell1.fill = fill  # 标色
                        comment = Comment(gap_com, '')
                        cell1.comment = comment
                    else:
                        if cell_value1 != '无':
                            cell1 = ws[f'{col_letter1}{excel_row}']
                            cell1.fill = fill  # 标色
                            comment = Comment(n_qk_com, '')
                            cell1.comment = comment
                    if pd.isna(cell_value2):
                        cell2 = ws[f'{col_letter2}{excel_row}']
                        cell2.fill = fill  # 标色
                        comment = Comment(gap_com, '')
                        cell2.comment = comment
                    else:
                        if cell_value2 != '无':
                            cell2 = ws[f'{col_letter2}{excel_row}']
                            cell2.fill = fill  # 标色
                            comment = Comment(n_sj_com, '')
                            cell2.comment = comment
                    if pd.isna(cell_value3):
                        cell3 = ws[f'{col_letter3}{excel_row}']
                        cell3.fill = fill  # 标色
                        comment = Comment(gap_com, '')
                        cell3.comment = comment
                    else:
                        if cell_value3 != '无':
                            cell3 = ws[f'{col_letter3}{excel_row}']
                            cell3.fill = fill  # 标色
                            comment = Comment(n_lx_com, '')
                            cell3.comment = comment
                else:
                    pass
        except Exception as e:
            err_info = f"处理单元格 {col_letter}{excel_row} 时出错: {str(e)}"
            print(err_info)
            error_list.append(err_info)


# 收缩压
def ssy(df, ws, col_name1, col_name2, error_list):
    """

    :param df: 预处理表格
    :param ws: 待生成表格
    :param col_name1: 身份证号
    :param col_name2: 收缩压
    :param error_list: 错误列表
    :return:
    """
    color = rules[col_name2]['不详']['color']
    # 设置单元格背景色
    fill = PatternFill(
        start_color=color,
        end_color=color,
        fill_type='solid'
    )
    gap_com = rules[col_name2]['不详']['comment']

    # _l1_min = int(rules[col_name2]['儿童']['less1']['min'])
    # _l1_max = int(rules[col_name2]['儿童']['less1']['max'])
    # _1to5_min = int(rules[col_name2]['儿童']['1to5']['min'])
    # _1to5_max = int(rules[col_name2]['儿童']['1to5']['max'])
    # _6to11_min = int(rules[col_name2]['儿童']['6to11']['min'])
    # _6to11_max = int(rules[col_name2]['儿童']['6to11']['max'])
    # _12to18_min = int(rules[col_name2]['儿童']['12to18']['min'])
    # _12to18_max = int(rules[col_name2]['儿童']['12to18']['max'])
    _cr_min = float(rules[col_name2]['成人']['min'])
    _cr_max = float(rules[col_name2]['成人']['max'])

    # _l1_com = rules[col_name2]['儿童']['less1']['comment']
    # _1to5_com = rules[col_name2]['儿童']['1to5']['comment']
    # _6to11_com = rules[col_name2]['儿童']['6to11']['comment']
    # _12to18_com = rules[col_name2]['儿童']['12to18']['comment']
    _cr_com = rules[col_name2]['成人']['comment']

    col_letter = column_map[col_name2]  # Excel数据列号（字母）

    # pattern = r'^[1-9]\d{5}(18|19|20)\d{2}(0[1-9]|1[0-2])(0[1-9]|[12]\d|3[01])\d{3}[\dX]$'
    # current_year = datetime.now().year
    # print(type(df.at[0,col_name3]))

    for row_idx in range(handle_num):

        excel_row = row_idx + 2  # Excel数据行号（标题在第1行）
        cell_value = df.at[row_idx, col_name2]
        cell_value = str(cell_value).replace("\t", '')
        # print(cell_value)

        try:
            cell = ws[f'{col_letter}{excel_row}']
            if pd.isna(cell_value) or str(cell_value) in ['无', '不详']:
                cell.fill = fill  # 标色
                comment = Comment(gap_com, '')
                cell.comment = comment
            else:
                cell_value = float(cell_value)
                if not _cr_min <= cell_value <= _cr_max:
                    cell.fill = fill  # 标色
                    comment = Comment(_cr_com, '')
                    cell.comment = comment
                # sfzh = df.at[row_idx, col_name1]
                # if not pd.isna(sfzh):
                #     sfzh = str(sfzh)
                #     if bool(re.match(pattern, sfzh)):
                #         birth_year = int(sfzh[6:10])
                #         age = current_year - birth_year
                #         if 0 <= age < 1:
                #             if not _l1_min <= cell_value <= _l1_max:
                #                 cell.fill = fill  # 标色
                #                 comment = Comment(_l1_com, '')
                #                 cell.comment = comment
                #         elif 1 <= age <= 5:
                #             if not _1to5_min <= cell_value <= _1to5_max:
                #                 cell.fill = fill  # 标色
                #                 comment = Comment(_1to5_com, '')
                #                 cell.comment = comment
                #         elif 6 <= age <= 11:
                #             if not _6to11_min <= cell_value <= _6to11_max:
                #                 cell.fill = fill  # 标色
                #                 comment = Comment(_6to11_com, '')
                #                 cell.comment = comment
                #         elif 12 <= age <= 18:
                #             if not _12to18_min <= cell_value <= _12to18_max:
                #                 cell.fill = fill  # 标色
                #                 comment = Comment(_12to18_com, '')
                #                 cell.comment = comment
                #         elif age > 18:
                #             if not _cr_min <= cell_value <= _cr_max:
                #                 cell.fill = fill  # 标色
                #                 comment = Comment(_cr_com, '')
                #                 cell.comment = comment
                #         else:
                #             pass

        except Exception as e:
            err_info = f"处理单元格 {col_letter}{excel_row} 时出错: {str(e)}"
            print(err_info)
            error_list.append(err_info)


# 舒张压
def szy(df, ws, col_name1, col_name2, error_list):
    """

    :param df: 预处理表格
    :param ws: 待生成表格
    :param col_name1: 身份证号
    :param col_name2: 舒张压
    :param error_list: 错误列表
    :return:
    """
    color = rules[col_name2]['不详']['color']
    # 设置单元格背景色
    fill = PatternFill(
        start_color=color,
        end_color=color,
        fill_type='solid'
    )
    gap_com = rules[col_name2]['不详']['comment']

    # _l1_min = int(rules[col_name2]['儿童']['less1']['min'])
    # _l1_max = int(rules[col_name2]['儿童']['less1']['max'])
    # _1to5_min = int(rules[col_name2]['儿童']['1to5']['min'])
    # _1to5_max = int(rules[col_name2]['儿童']['1to5']['max'])
    # _6to11_min = int(rules[col_name2]['儿童']['6to11']['min'])
    # _6to11_max = int(rules[col_name2]['儿童']['6to11']['max'])
    # _12to18_min = int(rules[col_name2]['儿童']['12to18']['min'])
    # _12to18_max = int(rules[col_name2]['儿童']['12to18']['max'])
    _cr_min = float(rules[col_name2]['成人']['min'])
    _cr_max = float(rules[col_name2]['成人']['max'])

    # _l1_com = rules[col_name2]['儿童']['less1']['comment']
    # _1to5_com = rules[col_name2]['儿童']['1to5']['comment']
    # _6to11_com = rules[col_name2]['儿童']['6to11']['comment']
    # _12to18_com = rules[col_name2]['儿童']['12to18']['comment']
    _cr_com = rules[col_name2]['成人']['comment']

    col_letter = column_map[col_name2]  # Excel数据列号（字母）

    # pattern = r'^[1-9]\d{5}(18|19|20)\d{2}(0[1-9]|1[0-2])(0[1-9]|[12]\d|3[01])\d{3}[\dX]$'
    # current_year = datetime.now().year
    # print(type(df.at[0,col_name3]))

    for row_idx in range(handle_num):

        excel_row = row_idx + 2  # Excel数据行号（标题在第1行）
        cell_value = df.at[row_idx, col_name2]
        cell_value = str(cell_value).replace("\t",'')
        # print(cell_value)

        try:
            cell = ws[f'{col_letter}{excel_row}']
            if pd.isna(cell_value) or str(cell_value) in ['无', '不详']:
                cell.fill = fill  # 标色
                comment = Comment(gap_com, '')
                cell.comment = comment
            else:
                cell_value = float(cell_value)
                if not _cr_min <= cell_value <= _cr_max:
                    cell.fill = fill  # 标色
                    comment = Comment(_cr_com, '')
                    cell.comment = comment
                # sfzh = df.at[row_idx, col_name1]
                # if not pd.isna(sfzh):
                #     sfzh = str(sfzh)
                #     if bool(re.match(pattern, sfzh)):
                #         birth_year = int(sfzh[6:10])
                #         age = current_year - birth_year
                #         if 0 <= age < 1:
                #             if not _l1_min <= cell_value <= _l1_max:
                #                 cell.fill = fill  # 标色
                #                 comment = Comment(_l1_com, '')
                #                 cell.comment = comment
                #         elif 1 <= age <= 5:
                #             if not _1to5_min <= cell_value <= _1to5_max:
                #                 cell.fill = fill  # 标色
                #                 comment = Comment(_1to5_com, '')
                #                 cell.comment = comment
                #         elif 6 <= age <= 11:
                #             if not _6to11_min <= cell_value <= _6to11_max:
                #                 cell.fill = fill  # 标色
                #                 comment = Comment(_6to11_com, '')
                #                 cell.comment = comment
                #         elif 12 <= age <= 18:
                #             if not _12to18_min <= cell_value <= _12to18_max:
                #                 cell.fill = fill  # 标色
                #                 comment = Comment(_12to18_com, '')
                #                 cell.comment = comment
                #         elif age > 18:
                #             if not _cr_min <= cell_value <= _cr_max:
                #                 cell.fill = fill  # 标色
                #                 comment = Comment(_cr_com, '')
                #                 cell.comment = comment
                #         else:
                #             pass

        except Exception as e:
            err_info = f"处理单元格 {col_letter}{excel_row} 时出错: {str(e)}"
            print(err_info)
            error_list.append(err_info)


# 人群分类
def rqfl(df, ws, col_name1, col_name2, error_list):
    """

    :param df: 预处理表格
    :param ws: 待生成表格
    :param col_name1: 身份证号
    :param col_name2: 人群分类
    :param error_list: 错误列表
    :return:
    """
    color = rules[col_name2]['无法判断']['color']
    # 设置单元格背景色
    fill = PatternFill(
        start_color=color,
        end_color=color,
        fill_type='solid'
    )
    gap_com = rules[col_name2]['无法判断']['comment']

    child_min = int(rules[col_name2]['儿童']['min'])
    child_max = int(rules[col_name2]['儿童']['max'])
    # general_min = int(rules[col_name2]['普通成人']['min'])
    # general_max = int(rules[col_name2]['普通成人']['max'])
    old_min = int(rules[col_name2]['老年人']['min'])

    child_com = rules[col_name2]['儿童']['comment']
    # general_com = rules[col_name2]['普通成人']['comment']
    old_com = rules[col_name2]['老年人']['comment']

    col_letter = column_map[col_name2]  # Excel数据列号（字母）

    pattern = r'^[1-9]\d{5}(18|19|20)\d{2}(0[1-9]|1[0-2])(0[1-9]|[12]\d|3[01])\d{3}[\dX]$'
    current_year = datetime.now().year
    # print(type(df.at[0,col_name3]))

    for row_idx in range(handle_num):

        excel_row = row_idx + 2  # Excel数据行号（标题在第1行）
        cell_value = df.at[row_idx, col_name2]
        # print(cell_value)

        try:
            cell = ws[f'{col_letter}{excel_row}']
            # 针对这个字段,只要身份证号正确,即可推断出年龄,所以判断逻辑为:身份证号有误->无法判断;身份证号正确,比对年龄,不匹配->对应提示
            # 优先判断身份证,而不是这个字段
            sfzh = df.at[row_idx, col_name1]
            if pd.isna(sfzh):  # 身份证号空白
                cell.fill = fill  # 标色
                comment = Comment(gap_com, '')
                cell.comment = comment
            else:
                sfzh = str(sfzh)
                if not bool(re.match(pattern, sfzh)):
                    cell.fill = fill  # 标色
                    comment = Comment(gap_com, '')
                    cell.comment = comment
                else:
                    if cell_value == '无':
                        birth_year = int(sfzh[6:10])
                        age = current_year - birth_year
                        if child_min <= age < child_max:
                            cell.fill = fill  # 标色
                            comment = Comment(child_com, '')
                            cell.comment = comment
                        elif age >= old_min:
                            cell.fill = fill  # 标色
                            comment = Comment(old_com, '')
                            cell.comment = comment
                        elif age < 0:
                            # 年龄为负数
                            cell.fill = fill  # 标色
                            comment = Comment(gap_com, '')
                            cell.comment = comment
                        else:
                            pass

        except Exception as e:
            err_info = f"处理单元格 {col_letter}{excel_row} 时出错: {str(e)}"
            print(err_info)
            error_list.append(err_info)
